select from list in VBA

A

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,053
Office Version
  1. 365
Platform
  1. Windows
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:

Code: 
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"
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
LilStevie, sounds wonderful but I don't have a clue about userforms. Have so far managed to create the ComboBox but I can't figure out how to assign my named range to it and then to make it appear within the code i have already written.
 
Upvote 0
thanks Lilstevie. after reading each and having a quick search around the respective websites, i must still be missing the good stuff. i have figured out the creation and have used UserForm1.show to have the UF pop up in the code. How do I then continue my code with the value that was selected. That is, the name selected by the user gets assigned to a variable called "KAM". In my existing code, KAM then goes into a few other spots.

Code: 
 Application.Goto Reference:="NameAccountManager"
    With ActiveCell
        .Value = Replace(StrConv(KAM, vbProperCase), " ", " ")
    End With

    'to re hide it
    Sheets("Group Template").Visible = xlVeryHidden
    Sheets("Last").Visible = xlVeryHidden

    ' order Outlet Tabs alphabetically before closing
etc etc etc

how do i get the userform up properly, assign the selected value to KAM, and then continue along my merry way??
 
Upvote 0
i've been on this all day and have come up against a wall. i can't get my combobox to assign the selected value to my variable.

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

what am i doing wrong????
 
Upvote 0
Is there a reason you are declaring KAM as a variant? From your description, it seems as though the variable would be better suited as a string? Variants are really really slow and HUGE memory hogs.
 
Upvote 0
can't quite remember. am quite new to declaring variables. i'll change it to string. will this help in the assignation problem?
 
Upvote 0
btw, 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
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Select from a term list, but input the corresponding key
Replies
1
Views
138
sijpie
sijpie
S
  • Question Question
Excel Drop Down Multiple Select/Remove & Auto Alphabetize Selections
Replies
0
Views
184
Sactown Steve
S
A
  • Solved
Filter formula based on drop down BUT with an option for all data
Replies
7
Views
229
myall_blues
myall_blues
K
  • Question Question
Select the Name in the Data Validation list on a cell value then the same name run the sub macro
Replies
9
Views
316
kpmsivaprakasam2003
K
FatalLordes
  • Question Question
Run a macro when an item is selected from a drop down list?
Replies
2
Views
247
FatalLordes
FatalLordes

Forum statistics

Threads
1,223,099
Messages
6,170,109
Members
452,302
Latest member
TaMere

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top