Dropdown/combo in visual basic

Question

Hello I'm trying without success to create a combobox in a userform.  

Combobox1 - Name

Combobox2 - Phone Extension

Combobox3 - Email

I would like that when I choose the name in combobox1 that the combobox 2 and combobox 3 will automatically populate.  

I've only got three names that each have a unique phone extension and email address.

I think this is called a dependent combobox that I am trying to set up.    

Thank you.

Moved from: Word / Windows 10 / Office 2016

Answer 1

I'm glad you were able to make it work.

For any other readers who need answers to your earlier question: All the code I showed goes behind the userform, along with the code for other text boxes and other controls on the userform.

Answer 2

First, unless you want users to be able to type values into the boxes that don't exist in the dropdown lists or in the code, then the second and third combo boxes should be replaced with text boxes, and the first combo box should have its .MatchRequired property set to True (to force the user to choose one of the names in the list).

Then this code will put the names into the combo box's list and select the first one into the box:

Private Sub UserForm_Initialize()

    With ComboBox1

        .AddItem "John Adams"

        .AddItem "Martin Jones"

        .AddItem "Tom Smith"

        .ListIndex = 0

    End With

End Sub

and this code will run when the user selects one of the names, putting the corresponding phone number and email address into TextBox1 and TextBox2:

Private Sub ComboBox1_Change()

    With ComboBox1

        Select Case .Value

            Case .List(0)

                TextBox1.Value = "555-4321"

                TextBox2.Value = "******@company.com"

            Case .List(1)

                TextBox1.Value = "555-4322"

                TextBox2.Value = "******@company.com"

            Case .List(2)

                TextBox1.Value = "555-4323"

                TextBox2.Value = "******@company.com"

            Case Else

                ' do nothing

        End Select

    End With

End Sub

Some notes on this: The list index of the combo box starts at 0, not at 1. The first item added by the UserForm_Initialize procedure becomes item 0, the next is item 1, and so on. The Select Case group of statements find the list item that matches the value in the box (one of them must match because MatchRequired = True) and sets the text boxes accordingly.

You'll need a command button on the userform (or two, one for OK and one for Cancel), with code in the corresponding Click procedure to transfer the contents of the three boxes into the current document.

The code is this simple only because you have just three names and six pieces of data to go with them. If you had much more data, you would want to store it in an Excel worksheet or other document to make it easier to edit, and the code would become more complex (but less likely to need updating).

Answer 3

Thank you very much for your reply. 

Users have been using the userform for a couple of years.  There are about 30-40 text boxes that the users will fill in then they press the submit button and all the data typed in populates the template.

Six of the text boxes are for what I have asked for help with here and if I can get this going it will be 4 less they have to type in.  

I just not sure where I would put that code you typed up - will I put it behind the userform with all the other code for the text boxes?  

The "Private Sub UserForm_Initialize()" macro would I just place that macro in with everything else?  Do I use a macro or do I just use code?

Sorry if I sound a bit lost - I am - I have created a lot of userforms, but simple ones would very much like to use the dropdown function.  

Thank you very much for your help.

Answer 4

Got it working - thank you very much for your help, I would never have got that.