Warning message in Excel macro if combobox null

Question

I create combo box selection using userform in Excel macro.

What I want to do is, to prevent the user to click OK without selecting a value.

Here is my code, I don't know what is wrong, the message box doesn't show.

Private Sub UserForm_Initialize()
    ComboBox1.RowSource = "Sheet1!G1:G" & Range("G" & Rows.Count).End(xlUp).Row
    ComboBox2.RowSource = "Sheet1!G1:G" & Range("G" & Rows.Count).End(xlUp).Row
End Sub


Private Sub CommandButton1_Click()
    If IsNull(ComboBox1) Then
        MsgBox ("ComboBox Has Data")
    End If

    Workbooks("Select Project.xlsm").Sheets("Sheet1").Range("B2").Value =  ComboBox1.Value
    Workbooks("Select Project.xlsm").Sheets("Sheet1").Range("C2").Value = ComboBox2.Value
End Sub

Can anybody help what is wrong with my code? Sorry, I'm new to VBA.

Do not test `IsNull(ComboBox1)` but `IsNull(ComboBox1.Text)` — Teasel, Oct 16 '17 at 09:40
the better way is to disable the OK button instead of popping a message — Rosetta, Oct 16 '17 at 09:51

Teasel · Accepted Answer · 2017-10-16T11:33:23.183

You're not checking the Text property of your ComboBox. You should process like this.

Private Sub CommandButton1_Click()
    If (ComboBox1.Text = "") Then
        MsgBox "ComboBox Has No Data"
        Exit Sub
    End If

    Workbooks("Select Project.xlsm").Sheets("Sheet1").Range("B2").Value =  ComboBox1.Value
    Workbooks("Select Project.xlsm").Sheets("Sheet1").Range("C2").Value = ComboBox2.Value

End Sub

What changed ?

I changed If IsNull(ComboBox1) Then with If (ComboBox1.Text = "") Then so this will check the Text property in your ComboBox.

I also added Exit Sub to leave the function if the ComboBox is empty so it doesn't commit the operation after.

score 0 · Answer 2 · answered Oct 16 '17 at 11:22

IsNull(ComboBox1) and IsNull(ComboBox1).Value will both never be true. Null is a value returned from a database if a field contains no value. You have to check if the value of the ComboBox is empty. An empty string in VBA is a string with the length 0, so you have to use on of those:

If Me.ComboBox1 = "" then ...
If Me.ComboBox1.Value = "" then ...
If Me.ComboBox1.Text = "" then ...

(For the difference between value and text-property see Distinction between using .text and .value in VBA Access)

Anyhow, I would go for the solution to enable/disable the button (as Rosetta suggested). Put a event-routine to the Combobox:

Private Sub ComboBox1_Change()
    Me.CommandButton1.Enabled = Me.ComboBox1.Value <> ""
End Sub

Warning message in Excel macro if combobox null

2 Answers2

What changed ?