Checking for Empty Excel Fields On Closing / Saving

sbecker61

Board Regular
Joined
Dec 29, 2009
Messages
52
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Board Regular
Joined
Dec 29, 2009
Messages
52
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

Well-known Member
Joined
Oct 30, 2009
Messages
1,197

ADVERTISEMENT

Hi,

You need a different Sub for that:

Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
You can add or substitute

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,037
Messages
5,526,402
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top