Adding drop down menu to a user form

[soberguy]

How do I add a drop down menu to a user form? Thanks for the help!

 

[Norie]

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]

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]

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]

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]

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]

soberguy

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

That's what I thought you meant.

 

[soberguy]

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]

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]

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.