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!

 

[HalfAce]

Man, you're typin' too fast for me!

Let me see if I got this.
You have a list of colors, some primary and some that're "Fuscia or those fancy colors", and you want only those in your list that have the word "Primary" in another column of their row to appear in your combobox's dropdown list?

If that's right then which column are your colors in and which column would have "primary" in it?

 

[HalfAce]

OK, try this and see if it helps.
Open the VBE and show your userform.
Right click it and choose View code.
Paste this into the userform's code module (The large white area on the right.)

Private Sub UserForm_Activate()
Dim LstRw As Long
'Determine the length of your list
LstRw = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To LstRw
    If Cells(x, 2) = "" Then _
        Me.ComboBox1.AddItem (Range("A" & x).Value)
Next x
End Sub

Now, you MUST make sure the RowSource property is left blank.
Then press Alt+Q to quit the VBE and get back to your sheet. Now you can experiment with adding / deleting values in column B to see what it does to your combobox list when you call the userform.

Is that what you're looking for?

 

[soberguy]

Looks like it could work. I suppose I should have mentioned the information is coming frmo column B instead. How would I change the above code to reflect that?

 

[HalfAce]

So... you want column B to get loaded into your combobox, only if what column is blank?

 

[soberguy]

Sorry. Column M must be blank in order to populate with column B.

 

[HalfAce]

That only takes a few minor changes.
Here you go...

Private Sub UserForm_Activate()
Dim LstRw As Long
'Determine the length of your list
LstRw = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
For x = 1 To LstRw
    If Cells(x, 13) = "" Then _
        Me.ComboBox1.AddItem (Range("B" & x).Value)
Next x
End Sub

 

[soberguy]

Excellent! Worked like a charm! Thank you kindly! Now how do I get it to ignore blanks? For some reason I have blanks in my list.

 

[HalfAce]

You're most welcome.
This will test for blanks in column B and ignore them.

Private Sub UserForm_Activate()
Dim LstRw As Long
'Determine the length of your list
LstRw = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
For x = 1 To LstRw
    If Cells(x, 2) <> "" And Cells(x, 13) = "" Then _
        Me.ComboBox1.AddItem (Cells(x, 2).Value)
Next x
End Sub