Excel VBA click on a cell open user form with values from cells to the right

In the attached worksheet, if I click on a cell in range H2:H500 I want the "UserForm1" to open and display the value in:
txtThk = cell I
txtWidth = cell J
txtLength = cell K

so if I click in cell H3, the userform would open and display:
txtThk = .11
txtWidth = 20
txtLength = 25

How can I do this in VBA?
Book1.xlsm
maverick0728Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached workbook and see if this is what you are trying to achieve.
Userform-with-selection.xlsm
0
MacroShadowCommented:
This is all the code you need, put it in the Sheet1 code module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("H2:H500")) Is Nothing Then
        UserForm1.txtThk = Sheets("Sheet1").Cells(Target.row, "I")
        UserForm1.txtWidth = Sheets("Sheet1").Cells(Target.row, "J")
        UserForm1.txtLength = Sheets("Sheet1").Cells(Target.row, "K")
        UserForm1.Show
    End If
End Sub

Open in new window

1
maverick0728Author Commented:
It opens the form and populates the textboxes correctly.  But it then goes into debug mode and stops on Userform1.Show.

See attached screen shot.
err.jpg
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martin LissOlder than dirtCommented:
I don't expect any points for this but you should consider changing

If Not Intersect(Target, Range("H2:H500")) Is Nothing Then

in the  Worksheet_SelectionChange to

If Not Intersect(Target, Range("H2:H" & Cells(Rows.Count, "I").End(xlUp).row)) Is Nothing Then


That way the userform won't show up with blank data if for example you click in H5.
1
Martin LissOlder than dirtCommented:
Please show the code that you are using in Sheet1.
0
maverick0728Author Commented:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
'If Not Intersect(Target, Range("H2:H500")) Is Nothing Then
If Not Intersect(Target, Range("H2:H" & Cells(Rows.Count, "I").End(xlUp).row)) Is Nothing Then
    row = Selection.row
    UserForm1.Show
End If
End Sub

Open in new window

0
MacroShadowCommented:
Going into debug mode isn't related to the code, which works fine. It is a pretty common occurrence when developing in the MS Office suite. The easiest way to rectify this is to click on the stop button in the VBE when the code isn't in break mode.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
@maverick0728: Do you get an error message?
0
maverick0728Author Commented:
No, it just goes into the debugger and stops on:
Userform1.Show
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If you see the VBA editor in the workbook I attached with the code, you will find that on a Standard module (Module1), I have declared a variable which is like below....

Public row As Long

Open in new window

Did you do the same with your workbook, if not the userform initialize event code would not work and the UserForm1.Show would produce an error.

You may also try the code suggested by MacroShadow if you are having issue with the code suggested by me.
0
Martin LissOlder than dirtCommented:
Then MacroShadow's comment in post ID: 40922756 should get it working for you, or you could just close and reopen the workbook.
0
maverick0728Author Commented:
I ended up changing the code to:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.EnableCancelKey = xlDisabled
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Range("H2:H" & Cells(Rows.Count, "I").End(xlUp).row)) Is Nothing Then
    row = Selection.row
    UserForm1.Show
  End If
  Application.EnableCancelKey = xlInterrupt
End Sub
0
MacroShadowCommented:
@maverick0728,
You are basically using sktneer's code, don't you think he/she should get points?
1
Martin LissOlder than dirtCommented:
I agree. He should get most or all of them.

@maverick0728, you can 'Request Attention' and a moderator will reopen the question.
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
+1 for both Martin & MacroShadow.

No issues with the points. Glad that OP's issue has been resolved. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.