COMBO BOX question

[ajm]

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 ????

[mikerickson]

Try putting

KAM=Me.txtKeyAccountManager.Value

before hidding the Userform.

[ajm]

i tried that Mike. problem seems to be in passing the variable from the userform across to the sheet module. so i am not wasting everybody's time, i have modified the code a little with the help of Chad (blazonQC on MrExcel: /board2/viewtopic.php?p=1356506#1356506) but still come up with this same problem: how to pass the value in the form to the variable within the sheet module.

[mikerickson]

See my responce in MrExcel. Basicaly, Public Variables declared in normal modules pass easier than those declared elsewhere. Put the declaration and sub in a normal module and have the sheet routine call that sub.

(I guess one of us should post the cross-post link)

[ajm]

thanks twice Mike. I tried to post the link in my previous post but had only notched up 4 posts. it seems you need 5 or more to post a link.

[Bob Phillips]

Still struggling with this?

Try qualifying the KAM variable with the Userform name.