Checking for Empty Excel Fields On Closing / Saving

[sbecker61]

Hi,

I found the following snippet of code (below) to check for required fields that are empty in an excel 2007 document. What I want to do is point out to the user that the fields are empty and are required to be filled in. I haven't tried it yet but reviews of the code seem to point that it will work for what I want to do. What I don't know how to do though is to activate this code when closing the document or saving it. I tried copying / pasting the code into the Excel spreadsheet. I close the document / save it and nothing happens even with empty fields. I'm sure this is an easy answer. Could someone help? Thanks in advance.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim r As Range, txt As String
With Sheets("Sheet1")
For Each r In .Range("h4,h5")
If IsEmpty(r) Then
txt = txt & r.Address(0, 0) & vbLf
End If
Next
End With
If Len(txt) > 0 Then
MsgBox "You need to fill:" & vbLf & vbLf & txt
Cancel = True
End If

 

[JamesW]

Hi there,

You need to paste it into your ThisWorkbook object (Alt+F11 on a sheet to open up the VB window then double click on ThisWorkbook on the left in the Project Window). Don't forget the End Sub at the end.

 

[VoG]

Hi. Press ALT + F11 to open the Visual Basic Editor. In the Project window double click ThisWorkbook then paste the code into the white space on the right.

 

[sbecker61]

Hi James,

Thanks for the quick answer. Your solution works great when exiting the document but it didn't work when I simply click on the Save icon. Any ideas when saving or should I just go with the Exit scenario.

Thanks again.

Steve

 

[JamesW]

Hi,

You need a different Sub for that:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim r As Range, txt As String
    With Sheets("Sheet1")
        For Each r In .Range("h4,h5")
            If IsEmpty(r) Then
                txt = txt & r.Address(0, 0) & vbLf
            End If
        Next
    End With
    If Len(txt) > 0 Then
        MsgBox "You need to fill:" & vbLf & vbLf & txt
        Cancel = True
    End If
End Sub

Simply add this underneath your Private Sub Workbook_BeforeClose sub.

 

[VoG]

You can add or substitute

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim r As Range, txt As String
With Sheets("Sheet1")
For Each r In .Range("h4,h5")
If IsEmpty(r) Then
txt = txt & r.Address(0, 0) & vbLf
End If
Next
End With
If Len(txt) > 0 Then
MsgBox "You need to fill:" & vbLf & vbLf & txt
Cancel = True
End If
End Sub

 

[sbecker61]

thank you all. works great.