ajm
07-09-2007, 04:22 PM
i've been on this all day (yesterday) and have come up against a wall. I have a similar query in MRExcel but haven't received a response overnight, so apologies to anyone who viewed this same query on the other site.
i can't get my combobox to assign the selected value to my variable so i can then use it later in another routine.
background: user is adding a new Outlet to a master file and then to a dependant file. I have code which does this already but wish to change an input box where the user types in a name to a combo box to avoid any spelling errors. the name then selected in the combo box is used later in the code to label and cell on a spreadsheet and then to form part of the name of the dependant file which needs to be opened. to do this, I have declared a variable called KAM.
in the sheet module, my user originally clicks a button which runs through certain routines before coming to UserForm1.Show. The userform then pops up. the range that appears in the drop down box is a list of names. the user should select one and hit OK. On clicking OK, the selected name should be assigned to the variable KAM and the userform then hides.
back in the sheet module, the original macro then continues by using the assigned variable KAM to name a cell, and then on to be used in the dependant file name.
I have declared the variable Public in the sheet module but when i test it, i get nothing. test used is simple:
Public KAM As Variant
Sub test()
UserForm1.Show
MsgBox KAM
End Sub
code attached to the userform is:
Private Sub CommandButton1_Click()
'check for a Key Account Manager Name
If Trim(Me.txtKeyAccountManager.Value) = "" Then
Me.txtKeyAccountManager.SetFocus
MsgBox "Please enter a Key Account Manager Name"
Exit Sub
End If
UserForm1.Hide
End Sub
what am i doing wrong or missing :banghead:????
i can't get my combobox to assign the selected value to my variable so i can then use it later in another routine.
background: user is adding a new Outlet to a master file and then to a dependant file. I have code which does this already but wish to change an input box where the user types in a name to a combo box to avoid any spelling errors. the name then selected in the combo box is used later in the code to label and cell on a spreadsheet and then to form part of the name of the dependant file which needs to be opened. to do this, I have declared a variable called KAM.
in the sheet module, my user originally clicks a button which runs through certain routines before coming to UserForm1.Show. The userform then pops up. the range that appears in the drop down box is a list of names. the user should select one and hit OK. On clicking OK, the selected name should be assigned to the variable KAM and the userform then hides.
back in the sheet module, the original macro then continues by using the assigned variable KAM to name a cell, and then on to be used in the dependant file name.
I have declared the variable Public in the sheet module but when i test it, i get nothing. test used is simple:
Public KAM As Variant
Sub test()
UserForm1.Show
MsgBox KAM
End Sub
code attached to the userform is:
Private Sub CommandButton1_Click()
'check for a Key Account Manager Name
If Trim(Me.txtKeyAccountManager.Value) = "" Then
Me.txtKeyAccountManager.SetFocus
MsgBox "Please enter a Key Account Manager Name"
Exit Sub
End If
UserForm1.Hide
End Sub
what am i doing wrong or missing :banghead:????