Prevent User to Select Combo Box correctly in Macro Excel

Question

I tried to create userform in Macro and create combo box selection for color.

I have successfully created the combo box as follow and prevent the user to continue without filling the combo box. here is the code:

Private Sub UserForm_Initialize()
ComboBox1.RowSource = "Sheet1!A1:A" & Range("G" & Rows.Count).End(xlUp).Row
End Sub
--------------------------------------------
Private Sub CommandButton1_Click()

If ComboBox1.Text = "" Then
    MsgBox "Please Select Color!"
    Exit Sub

Else
Sheets("Sheet1").Range("B1").Value = ComboBox1.Value
ActiveWorkbook.Close
End If

However, the user can type random text on the combo box and I can't prevent it. Is there any way to make the user can only select the value from provided source? here is the case.

score 2 · Accepted Answer · answered Oct 16 '17 at 12:51

2

Set the Combo style to fmStyleDropDownList

answered Oct 16 '17 at 12:51

AndyW

410
5
17

Prevent User to Select Combo Box correctly in Macro Excel

1 Answers1