Search code examples
excelvbacellisnullorempty

How to check if a cell is empty using VBA

I'm trying to make a form on excel so that when people don't fill in certain cells, they will get pop-up error messages. So far, I made this code:

If Len(I8) = 0 Then
MsgBox "Please enter Required Date", vbExclamation
Exit Sub
ElseIf Len(D11) = 0 Then
MsgBox "Please enter your name", vbExclamation
Exit Sub
ElseIf Len(H11) = 0 Then
MsgBox "Please enter Work Phone", vbExclamation
Exit Sub
ElseIf Len(L11) = 0 Then
MsgBox "Please enter Home Phone", vbExclamation
Exit Sub
End If
MsgBox "All mandatory fields have been entered", vbExclamation
End Sub

Which seemed to work, but when I added text to the cell I8, the "Please enter Required Date" msgbox popped up anyways.

I've also tried using Count(I8) = 0, and IfEmpty(I8) = True, but neither were recognized properly in vba.

Also, if I want a pop-up message to appear when a cell is not filled out if they picked "YES" from a drop-down list, what would the function be? So far I've written

ElseIf Range("D28") = "P16" And Len(Range("M30")) = 0 Then
MsgBox "Please Select whether this file is classified as confidential", vbExclamation
Exit Sub

But I need a defined function and I'm not sure what to pick. Is there a longer way to do it to ensure that both cells are filled out if they pick yes in the first cell?

Solution

  • When you use something like If Len(I8) = 0 Then in VBA, it will assume I8 is a variable. Since you obviously don't have that variable defined, Len(I8) will always be zero.

    What you should do is use Range("I8") or even ActiveSheet.Range("I8").Value instead to get the value that is actually in that cell.

    So your code should change to:

    If Len(Range("I8")) = 0 Then
MsgBox "Please enter Required Date", vbExclamation
Exit Sub
ElseIf Len(Range("D11")) = 0 Then
MsgBox "Please enter your name", vbExclamation
Exit Sub
ElseIf Len(Range("H11")) = 0 Then
MsgBox "Please enter Work Phone", vbExclamation
Exit Sub
ElseIf Len(Range("L11")) = 0 Then
MsgBox "Please enter Home Phone", vbExclamation
Exit Sub
End If
MsgBox "All mandatory fields have been entered", vbExclamation
End Sub

    To avoid these kinds of confusing issues, you can specify Option Explicit at the very top of the module. This causes excel to throw an error when you use undeclared variables like that instead of just silently guessing what it should do with it.