Adding drop down menu to a user form

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
This can be done but isn't easy.

You either need to use the Windows API, or simulate a menu using controls which you programatically make visible/invisible.

Have you tried a search on this forum or the Web?
 

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
Are you referring to adding a menu of valid values from which to choose from, and the item selected then becomes the value on the form?

If so, use the Data Validation menu option across your cells for which you want the drop down to be effective. Then select List as the option for the data validation to occur against. Use a named range to identify the list of valid values, and reference that named range on the data validation source.

HTH
 

soberguy

New Member
Joined
Nov 28, 2005
Messages
13
Yes I have, but I really haven't found much of anything. In VBA there is the option to add a list box. Is that not the same as a drop down menu? It's just a simple user form, and one of the questions is to pick from a list of only 4 or 5 options. I didn't think it would that tough. Any better routes to go?

EDIT: Perhaps I should add I'm not nearly as experienced as some of you. The more detailed help, the better, or better yet, even a simple example of a listbox in a user form, just so I can see the code would be awesome. I am just learning VBA, and don't have a guide book to work off of.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453

ADVERTISEMENT

Instead of the ListBox, is it not a ComboBox that you're interested in?
This will give you the dropdown arrow and allow the user to select one of the items in your list.
 

soberguy

New Member
Joined
Nov 28, 2005
Messages
13
AHA! A combo box indeed! Thank you kindly for pointing out what I really wanted. See, I really am new to this! :P

OK, so now, how do I populate that combo box with option off of my worksheet? For example, I'll have names in Column A. I want those names in my combo box, but only if column B is empty. Make sense?

And for extra credit:
What's the listbox then?
Is there some good suggested material for VBA basics?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

soberguy

Do you mean an actual menu like the File menu in Excel?

That's what I thought you meant.:oops:
 

soberguy

New Member
Joined
Nov 28, 2005
Messages
13
Oh goodness. I clearly am not familiar enough with excel, as i don't seem to be using the proper terms. Let me try this:

I have a user form. Person's name, birthdate, and say, favorite color. But, favorite color is limited to primary colors, ie, red, orange, yellow, green, blue, indigo, and violate. (Done this way since, as a man, I don't know what Fuscia or those fancy colors are.) So, I want a little drop down menu to select one of those primary colors. Apparently this would be called a combo box? Now, I've got a fancy list of colors. But only certain colors are labeled as primary. I only want primary colors in the "combobox". Make sense?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
soberguy said:
OK, so now, how do I populate that combo box with option off of my worksheet? For example, I'll have names in Column A. I want those names in my combo box, but only if column B is empty.
Here's a standard way of populating your combobox:
Bring up your userform in the VBE.
With the ComboBox selected, press F4 to show the properties window.
You set the source for your list in the property called “RowSource” – Just enter the range that your list is in on the worksheet. (a one column wide list only.) Note that you can use a dynamic named range if your list will grow.

If you want the combobox to only get populated under certain conditions, then it can be done with code in your userform Initialize or Activate event(s).

What do you want to have happen if column B is not empty?
 

soberguy

New Member
Joined
Nov 28, 2005
Messages
13
If there's a value in column B, I don't want the value in column a to appear. The user form basically populates column B. So, if there is a value in column B, then there's no need to have column A in the list to choose from. Make sense? In other words, the list box is populated only if column B is empty.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,086
Members
412,310
Latest member
mark884
Top