CODEWITHSUNDEEP
excelvba
Ramiro Martinez
Ramiro Martinez

Reputation: 13

Show cells that are empty

I used IsEmpty() to determine whether a cell has a value in it and display a message that cells are missing data before letting the file print. I want to be able to specify which cells are missing by showing a message that states which cells have no data and not let the file print until all fields have a value.

Also, instead of displaying the cell address that has no value, would it be possible to show the defined name for that cell within the worksheet? I.e C2 is Name, F2 is Date....

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If IsEmpty([C2]) Or IsEmpty([F2]) Or IsEmpty([K2]) Or IsEmpty([N2]) _
    Or IsEmpty([C3]) Or IsEmpty([A8]) Or IsEmpty([F8]) _
    Or IsEmpty([C34]) Or IsEmpty([C35]) _
    Or IsEmpty([C36]) Or IsEmpty([C37]) Or IsEmpty([G35]) _
    Or IsEmpty([G36]) Or IsEmpty([G37]) Or IsEmpty([I35]) _
    Or IsEmpty([I36] Or IsEmpty([I37]) _
    Or IsEmpty([L11]) Or IsEmpty([L18]) Or IsEmpty([L25]) _
    Or IsEmpty([J28]) Or IsEmpty([N28]) Then
        Cancel = True
        MsgBox ("Missing Cell. Please verify form!")
    End If
End Sub

Upvotes: 0

Views: 55

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

another take:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim arr
arr = Array("C2", "F2", "K2", "N2", "C3", "A8", "F8", "C34:C37", "G35:G37", "I35:I37")

With Worksheets("Sheet1") ' Change to your sheet.
    Dim rng As Range
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        If rng Is Nothing Then
            Set rng = .Range(arr(i))
        Else
            Set rng = Union(rng, .Range(arr(i)))
        End If
    Next i
    Dim rng2 As Range
    Set rng2 = .Range("A2:I37").SpecialCells(xlCellTypeBlanks)
    Dim oRange As Range
    Set oRange = Intersect(rng, rng2)
    If Not oRange Is Nothing Then
        MsgBox ("Missing Cell. Please verify form!") & vbNewLine & oRange.Address
        Cancel = True
    End If
End If
End Sub

Upvotes: 0

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

You may try something like this...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rng As Range, cell As Range
Dim EmptyFound As Boolean
Dim str As String
Set rng = Range("C2:C3,F2,K2,N2,A8,F8,L11,L18,L25,J28,N28,C34:C37,G35:G37,I35:I37")
str = "The following cells are empty. Please verify form!" & vbNewLine & vbNewLine
For Each cell In rng
    If IsEmpty(cell) Then
        EmptyFound = True
        str = str & cell.Address(0, 0) & vbNewLine
    End If
Next cell
If EmptyFound Then
    Cancel = True
    MsgBox str, vbExclamation, "Empty Cells Found!"
End If
End Sub

Upvotes: 1

Related Questions