1

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?

desperatehipster
  • 23
  • 1
  • 1
  • 4
  • 1
    whats `I8`? Shouldn't that be `Range("I8")` possibly or is that a control name –  May 14 '14 at 13:53
  • You might try checking out this answer : [Thread](http://stackoverflow.com/questions/13360651/excel-how-to-check-if-a-cell-is-empty-with-vba). Hope this helps. – Weava May 14 '14 at 13:53
  • 1
    If `I8` is a cell/range, then it should be `If Len(Range("I8").Value) = 0 ...` per @mehow. Cheers. – David Zemens May 14 '14 at 13:54
  • 1
    you might also want to put in `Option Explicit` as the 1st line of all your code - this error would have been flagged immediately you tried to run it – SeanC May 14 '14 at 14:24
  • What does that do? Do I simply write `Option Explicit` on the line above the `If Len(Range("I8")) = 0 Then` line? – desperatehipster May 14 '14 at 14:26
  • follow sean's instructions. `Option Explicit` **as the 1st line of all your code** – guitarthrower May 14 '14 at 15:19

2 Answers2

3

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.

neelsg
  • 4,802
  • 5
  • 34
  • 58
  • Thanks. :) I've changed my code as you suggested, but when I put `Option Explicit` at the top of the code it simply says "Compile error: Invalid inside procedure" – desperatehipster May 14 '14 at 14:48
1

See comments for why your code doesn't work. The following are statements that will evaluate True if your cell is empty:

IsEmpty(myCell)
myCell.Value = vbNullstring
Len(myCell) = 0

Enjoy!

CodeJockey
  • 1,922
  • 1
  • 15
  • 20