Drop Down on User Form

Microsoft Excel 2003 (full product)
September 23, 2009 at 12:58:50
Specs: Windows XP
I'm having trouble isolating the individual options for a Drop Down control on the Excel User Form tab.

I have entered my options in the Text Box. That didn't work. Then I tried the value box. That didn't work as well. All the options are in one line instead of being vertically listed.


See More: Drop Down on User Form

Report •

September 23, 2009 at 13:35:50
How do you remove posts? I was able to answer this...

Report •

September 23, 2009 at 15:02:38

I suggest that you use a proper form and not a control on the worksheet.

It takes a little longer to setup but the effect is so much better.

It is visually better, and controls, labels and buttons can be grouped logically.
The code is all in one place and variables can be shared between controls, labels and of course cells on the spreadsheet.

Try the following:

Open a new spreadsheet and save it as "Sample.xls"
Open the Visual Basic editor = Alt+f11 is easiest or Tools- Macro - Visual Basic Editor

On the left should be the Project Explorer pane and you will see VBAProject (Sample.xls)
(If not visible go to View - Project Explorer)

Expand VBAProject (Sample.xls)
You will see Microsoft Excel Objects with one entry for the whole Workbook and one entry for each worksheet

We are going to add two new objects to Sample.xls.

One item will be a form and the other item will be a module.

Right click on VBAProject (Sample.xls) and select Insert - UserForm
Repeat for Module (not Class Module)

You will now have a longer 'tree' with a UserForm1 and a Module1
It would be good to give these meaningful names, but just for this demo. keep all the default names

Double click on UserForm1
Up comes a rectangle labeled UserForm1 with a grid of dots

Now we will add four items to the form:
One Combo Box,
Two Labels and
One Command Button
There should be a toolbox showing to select from (if not click View - Toolbox)

It doesn't matter where you put these items on your form - moving them later will not affect how they work

Now back to your workbook - no need to close the VBA window -
On sheet 2 enter the following from Cell B1 to cell B8

B1	Combo Box 1 Options
B2	Option 1
B3	Option 2
B4	Option 3
B5	Option 4
B6	Option 5
B7	Option 6
B8	Option 7

Now back to the form

With a form you need to have some Initialization done that gets your form ready for use:
At the top of the Project Explorer are three icons. The leftmost is View Code and the middle is View Object (the form)

Click on view code

Code belongs to various items - the User Form itself and the various tools or objects on the form as well as one general area
At the top of the code window, use the left hand drop down to select User Form
You will get a generic Click event,
but ignore it and from the right side drop down select Initialize

Whatever code gets placed in this section will run as soon as the form is brought to life!

Enter this code which will populate the ComboBox with the list we created on Sheet 2 and it will select which list item will be in view when the form opens. (Later on try changing the ListIndex based on one of those International values eg
Me.ComboBox1.ListIndex = Application.International(xlNoncurrencyDigits) -1
I have two digits set so option 2 shows):

(The first and last lines of the following code should already be on the screen for you)

Private Sub UserForm_Initialize()
Me.ComboBox1.List = Workbooks("Sample.xls").Worksheets("Sheet2").Range("B2:B8").Value
Me.ComboBox1.ListIndex = 0
Me.Label1.Caption = "You Selected :"
End Sub

Try clicking on a period after the 'Me' then delete it and reenter it. You will get a list of objects on your form - sure beats trying to remember all the objects names! Use the Tab button to select an object.
Try the same for the period after ComboBox1 and you will see all the things available for a ComboBox.

Now select the ComboBox Object from the left side drop down and Change from the right side drop down
Enter the following code:

Private Sub ComboBox1_Change()
Me.Label2.Caption = Me.ComboBox1.Value
End Sub

Finally enter this code for CommandButton1 - Click:

Private Sub CommandButton1_Click()
Workbooks("Sample.xls").Worksheets("Sheet2").Range("C1").Value = "Selected by User at " & Format(Now(), "hh:mm")
Workbooks("Sample.xls").Worksheets("Sheet2").Range("C2").Value = Me.ComboBox1.Value
End Sub

You now have a working Form

To get the form to open we can add a subroutine in Module1
Double click module 1 in the Project Explorer

Enter this code:

Sub StartMyForm()
UserForm1.Show vbModeless
End Sub

vbModeless means that when the form is shown you can still get out of the form and got to the worksheet
If it was Modal you couldn't get out of the Form, except by closing it.

Now you're ready for a test.

Goto Module 1 and click inside the code - press f8 and the first line of code is highlighted (f8 is single step mode - good for testing code line by line) now hit f5 and the code will run.

The form opens !

Move windows about so that you can see Sheet 2 and your list.

Back to the form and you should see that Option1 shows in the ComboBox and label 1 says "you selected:"
and label 2 says "Option1".

Use the ComboBox and select different options
Label 2 will change to reflect what you selected and
Now click the CommandButton1 and a message like the following will appear on Sheet 2 next to your list of options:

Selected by User at 17:34
Option 4

Change the ComboBox selection and click the command button and it all changes.

I didn't put a Quit button on the Form so to exit use the 'x' on the window - bad programming - but this was a quick demo to get you started.

Hope this gives you a start and shows what is possible.


Report •

Related Solutions

Ask Question