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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,712
Messages
5,833,267
Members
430,200
Latest member
ADLHMA2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top