Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass variable from user form to macro
I'm using a user form to get a selection from a customer, which I then will
place in a cell in the worksheet. How do I pass this selection from the user form to the controlling sub? Sub PopulateListWithShingleTypeRange() 'Shingle Type Selection Dim Selection As String Dim ShingleSeletion As String For Each x In Sheet1.Range("StblShingleType") UserForm1.ListBox1.AddItem x.Value Next UserForm1.Caption = "Shingle Selection" With UserForm1.Label1 ' Set the text of the label. .Caption = "Select the Shingle Type" End With UserForm1.Show Range("InptShingleType").Value = Selection UserForm1.Hide End Sub I've tried declaring a variable with the same name in the user form, but that didn't work either. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass variable from user form to macro
Sub PopulateListWithShingleTypeRange()
'Shingle Type Selection Dim Selection As String Dim ShingleSeletion As String For Each x In Sheet1.Range("StblShingleType") UserForm1.ListBox1.AddItem x.Value Next UserForm1.Caption = "Shingle Selection" With UserForm1.Label1 ' Set the text of the label. .Caption = "Select the Shingle Type" End With UserForm1.Show Range("InptShingleType").Value = Userform1.ListBox1.Value Unload Userform1 End Sub In the userform you should have code to hide the userform. An alternate method would be to use the click event of the listbox in the userform Private Sub Listbox1_Click() if listbox1.ListIndex < -1 then Range("InptShingletype").Value = Listbox1.Value end if End Sub -- Regards, Tom Ogilvy "r wilcox" wrote in message ... I'm using a user form to get a selection from a customer, which I then will place in a cell in the worksheet. How do I pass this selection from the user form to the controlling sub? Sub PopulateListWithShingleTypeRange() 'Shingle Type Selection Dim Selection As String Dim ShingleSeletion As String For Each x In Sheet1.Range("StblShingleType") UserForm1.ListBox1.AddItem x.Value Next UserForm1.Caption = "Shingle Selection" With UserForm1.Label1 ' Set the text of the label. .Caption = "Select the Shingle Type" End With UserForm1.Show Range("InptShingleType").Value = Selection UserForm1.Hide End Sub I've tried declaring a variable with the same name in the user form, but that didn't work either. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer variable to user Form | Excel Discussion (Misc queries) | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming | |||
Help w/ macro to run user form | Excel Programming | |||
Is it possible to open the VBA form with a link in a sheet and to pass variable from a cell to the VBA form? | Excel Programming | |||
Calling variable from user form | Excel Programming |