select from list in VBA

[ajm]

how do you create a drop down list for the user to select from in vba? In the code I have, the user has been manually typing in the name of the Key Account Manager:

KAM = InputBox("Enter Key Account Manager's Name:", "Name Required")

I want to remove any margin for error by having them select from a list instead. My named range with the list of names in is called "Key_Account_Manager"

 

[blazonQC]

Ok bud,

I just made a combo box example for you that displays 2 methods to assign a value from a c box to a variable. Be warned...... It is commented to hell, lol. Just try to look through it though. I tried to explain EVERY little step I took. There is only code in the form object and in the 'ThisWorkbook' object so it should make it easy to locate.

Here is the file: http://www.mediafire.com/?edt9tkvatol

And oh yeah, to answer you last question.... Yes, declaring the variable as a Variant COULD be causing you some issues, but it seems unlikely. There is probably something you are just not noticing.

Hope this helps!

Chad

 

[mikerickson]

KAM=Me.txtKeyAccountManager.Value

is missing from the code.

 

[ajm]

thanks CHad. essentially, it looks like you have to pass the value to a cell on a worksheet first, and then assign the variable. does that sound about right? if so, any ideas why it can't just be assigned to a variable straight away?

 

[blazonQC]

nah, you don't have to assign it to a cell first, I was just showing you how you could then pass the varible to a cell if so desired. The first thing I did for both events was pass the value of the combo boxes to the variable:

Private Sub cmbTest_Change()

KAM = cmbTest.Text '< Assign text from the c box to KAM

Worksheets(1).Cells(2, 4).Value = KAM '< Assign value of KAM to the cell

Call MsgBox("KAM = " & KAM, vbOKOnly, "What is KAM?")

end sub

Like I said the line 'KAM = cmbTest.Text' is where you're passing the info to the variable. The other two lines are just for show and debugging so you could see that KAM was indeed holding the proper value.

Chad

 

[ajm]

i am still having trouble. i can assign the value to the cell but don't get anything in the message box. I have moved the declaration of the variable to
ThisWorkbook (as you had it); I have personalised the code (in the userform) to:

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
KAM = Me.txtKeyAccountManager.Value
Worksheets("Lists - Product, KAMs etc").Range("H2").Value = KAM


cmbKAM.Hide
End Sub

and finally, in the sheet itself:

Sub test()
cmbKAM.Show


Call MsgBox(KAM)

End Sub

with the message box in the userform itself, it shows up the proper result. in both your example and mine variation, the value gets properly passed to the worksheet but not between modules.

any further clues?

 

[mikerickson]

Is Sub Test in a Sheet Module or a normal module?
I'd put it in a normal module. And check the the declaration to make sure its Public

Public KAM as Variant: Rem or String

 

[ajm]

mike, just responded to you on VBAX. Sub Test is in a sheet at the moment as the routine this will go into is in a sheet. i guess it could just as easily go into a module and get called from the sheet? wouldn't i still face the problem of how to get the variable KAM recognised in the sheet though?

 

[mikerickson]

I'd declare KAM in the normal module.
As a test, this in a sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
kam = "x"
Call test
MsgBox kam
End Sub

and this in a normal module

Public kam As String

Sub test()
kam = "smith"
End Sub

produced "smith".
Moving the declaration to the sheet module,

Public kam as String

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
kam = "x"
Call test
MsgBox kam
End Sub

Sub test()
kam = "smith"
End Sub

produced "x"

 

[ajm]

and there we have it. Thanks mike. The declaration must go into the Module and not ThisWorkbook, nor the sheet.

thanks Chad for your help also.

 

[ajm]

one last question on this one Mike. How do I ensure the form is Unloaded before use each time, so that when the user .shows the form, it pops up clean?