CODEWITHSUNDEEP
excelvba
Kat
Kat

Reputation: 13

Warn if blank/empty cells

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.

Upvotes: 1

Views: 2216

Answers (3)

Profex
Profex

Reputation: 1390

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

Upvotes: 0

jblood94
jblood94

Reputation: 16986

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

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21657

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

Upvotes: 0

Related Questions