user form drop down box

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

I am creating a user-form for data entry into a particular worksheet.

For one of the fields I'd like to have a drop down box (which I can figure
out how to get there) with two choices to pick from (which I can't figure
out how to get the choices to show up).

Can someone please tell me how to get the choices to show up in the drop
down box of a user form?

Thank you,
Glenn
 
Hi Glenn

Private Sub UserForm_Initialize()
With Me.ComboBox1
.Style = fmStyleDropDownList
.AddItem "Beer"
.AddItem "Cerveza"
.ListIndex = 1
End With
End Sub

HTH. Best wishes Harald
 
Thank you for asking that question for it gives me the chance to give a
little back to the NG after receiving a whole lot of help from it. I
just went through what you are trying to do this morning.

Here is what I did but there may be a better way.


Private Sub UserForm_Initialize()

YourComboBoxName.AddItem "Your First Item"
YourComboBoxName.AddItem "Your Second Item"

' and so forth

End Sub

Hope this helps.
 
Thanks to both of you. This worked. I am curious about something though -
in the event of a long list or one that is always changing, I can see where
it would be easier to have the data for the drop down box stored on a
worksheet somewhere.

Is that possible?
 
Good thinking. There are many ways to do that. Here is three common
solutions; an array, the rowsource property and a loop:

Private Sub UserForm_Initialize()
ComboBox1.List = Sheets(1).Range("A1:A200").Value
End Sub

Private Sub UserForm_Initialize()
ComboBox1.RowSource = "=Sheet1!A1:A200"
End Sub

Private Sub UserForm_Initialize()
Dim R As Long
For R = 1 To 200
ComboBox1.AddItem Sheets(1).Cells(R, 1).Text
Next
End Sub

HTH. Best wishes Harald
 
Thanks! Now for my next question -

Say I have two (or more combo boxes). Is there a way to make the choices
that pop up in the second one dependent upon what was picked in the first
one? (For instance if the first one has two choices - "men" and "women",
and if you pick "men" only the names of men will come up in combo box 2?

Thanks again for your help. I really appreciate it.
 
Sure, several ways. You should be able to solve this by now ;-)

HTH. best wishes Harald
 
Well, I can figure out one way...to make up a user form that has the first
drop down box in it along with a command button that will load a different
user form dependent on what was chosen in the first drop down box.

But, I'd really like this to be all on the same form, to make the process go
quicker.
 
This solution uses names in Sheet1 A column, and M or F in B column
indicating male / female.

Private Sub UserForm_Initialize()
With Me.ComboBox1
.Style = fmStyleDropDownList
.AddItem "Male"
.AddItem "Female"
.ListIndex = 1
End With
End Sub

Private Sub ComboBox1_Click()
Dim R As Long
ComboBox2.Clear
Select Case ComboBox1.ListIndex
Case 0
For R = 1 To 200
If Sheets(1).Cells(R, 2).Value = "M" Then _
ComboBox2.AddItem Sheets(1).Cells(R, 1).Value
Next
Case 1
For R = 1 To 200
If Sheets(1).Cells(R, 2).Value = "F" Then _
ComboBox2.AddItem Sheets(1).Cells(R, 1).Value
Next
Case Else
End Select
End Sub

HTH. Best wishes Harald
 
Back
Top