Warn if blank/empty cells

Question

I'm trying to pop up a warning dialog if one of the cells in a range is blank.

Excel is popping up the warning when a cell is populated by a data validation drop down menu.

The code works on a range that doesn't contain data validation drop downs.

All data are strings and the cells are formatted as "General".

ActiveSheet.Range("I3:I10").Select
For Each cell In Selection
    If cell.Value = "" Then
        If MsgBox("Customer information missing.  Okay to proceed; Cancel to stop and fill in missing information.", vbOKCancel) = vbCancel Then
            Exit Sub
        Else
            Exit For
        End If
    End If
Next

The issue seems to stem from cells being merged across multiple columns, so Excel is checking each cell I3:K10 and finding J3:K10 blank. Unmerging the cells isn't an option.

A value added via a validation list should behave the same way as if you'd typed in the value directly, so if you see a value but the VBA reports the cell is empty then something else is going on. — Tim Williams, Jun 04 '18 at 23:16
There a `COUNTBLANK` worksheet formula you can use on the worksheet or directly in VBA with `Application.WorksheetFormula.` — ashleedawg, Jun 04 '18 at 23:17
I'll bet that your code is not looking at the workbook that you think it is... See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) — Profex, Jun 04 '18 at 23:18
@Profex it's the only workbook open (and I can see the selected range when I step through the code), so I doubt it's looking anywhere else. — Kat, Jun 04 '18 at 23:25
Add this on the line before the message `Debug.Print "'[" & Cell.Parent.Parent.Name & "]" & Cell.Parent.Name & "'!" & Cell.Address "= """ & Cell.Value & """"` — Profex, Jun 04 '18 at 23:26
@urdearboy the =vbNull worked for my initial issue, but created a new problem: blank cells aren't being detected now! — Kat, Jun 04 '18 at 23:29
@Profex before the if statement containing the message box? If so, it doesn't seem to do anything. My warning still pops up when it isn't supposed to, but I don't see a debug printout anywhere. — Kat, Jun 04 '18 at 23:32
in the VBE show the debug window by clicking `View->Immediate Window (Ctrl+G)` — Profex, Jun 04 '18 at 23:35
@Profex okay, I see now. It looks like the code is referencing column J instead of column I. The cells are merged across I, J, and K, and I have seen that cause weird behavior before. Unmerging the cells isn't really an option in this case, so I'll have to look for a workaround. Thank you for helping identify the problem! — Kat, Jun 04 '18 at 23:48
Ahh, that makes sense now...drop the select and it will work. The selection is actuall `Range("I3:K10")`. So after it checks column I it moves onto Column `J` and then `K` — Profex, Jun 04 '18 at 23:50
@Profex perfect! Doing it without selecting the cells works perfectly. Thank you! — Kat, Jun 04 '18 at 23:53

jblood94 · Answer 1 · 2018-06-04T23:33:52.207

If a cell is set up with data validation using a list and one of the cells in the list range is blank, the cell will be considered blank even if the user has selected the blank cell from the drop-down. However, if you also want to check if the cell is empty and does not have data validation, you can use the following (thanks to Determine if cell contains data validation)

Dim blnValidation As Boolean
Dim cell As Range

ActiveSheet.Range("I3:I10").Select

For Each cell In Selection
    If cell.Value = "" Then
        blnValidation = False

        On Error Resume Next
            blnValidation = Not IsNull(cell.Validation.Type)
        On Error GoTo 0

        If Not blnValidation Then
            If MsgBox("Customer information missing.  Okay to proceed; Cancel to stop and fill in missing information.", vbOKCancel) = vbCancel Then
                Exit Sub
            Else
                Exit For
            End If
        End If
    End If
Next

I still get the same improper popup when the cell isn't blank. >.< There are only two choices in the drop down, and no blanks in the validation range. — Kat, Jun 04 '18 at 23:41

score 0 · Answer 2 · answered Jun 04 '18 at 23:21

0

There a COUNTBLANK function but in this case you're probably better off with the COUNT Application.WorksheetFunction.

You could replace your loop with:

If Application.WorksheetFunction.Count(Range("I3:I10")) < 8 Then
    'missing data - notify user here
End If

answered Jun 04 '18 at 23:21

ashleedawg

20,365
9
72
105

Still pops up my "missing data" warning even when all cells are populated. :/ `For Each cell In Selection` `If Application.WorksheetFunction.Count(Range("I3:I10")) < 8 Then` `If MsgBox("Order processing information missing. Okay to proceed; Cancel to stop and fill in missing information.", vbOKCancel) = vbCancel Then` `Exit Sub` `Else` `Exit For` `End If` `End If` `Next` – Kat Jun 04 '18 at 23:36

score 0 · Accepted Answer · answered Jun 04 '18 at 23:54

0

So, I was wrong with my initial guess, but the cure still would've worked.

How to avoid using Select in Excel VBA

The selection is actually Range("I3:K10"). So after it checks column I it moves onto Column J and then K

answered Jun 04 '18 at 23:54

Profex

1,370
8
20

Warn if blank/empty cells

3 Answers3