Trying to run macro on the BeforeSave event

[dmagoo22]

First off, I'm new to writing macros in excel. I have a created a
simple macro to check if a cell is blank and if it is to change the
highlighted color to yellow as a required field indicator. The macro
runs fine when I run it manually but I want this to run when the user
tries to save the document so it will not let them save their changes
until all Required fields are filled in. Here is my code. Can you let
me know what I'm doing wrong:


Sub Workbook_BeforeSave()
Dim rowcount As Integer, msg As String, col As String, cnt As
Integer

msg = "The highlighted Fields are required." & vbCrLf & _
"Please fill in all required fields."

lastrow = (Cells(Rows.Count, "A").End(xlUp).Row) - 1

For i = 2 To lastrow
If Cells(i, "A").Value = "" Then
Range("A" & i).Interior.ColorIndex = 6
End If
Next i

For i = 2 To lastrow
If Cells(i, "B").Value = "" Then
Range("B" & i).Interior.ColorIndex = 6
End If
Next i

For i = 2 To lastrow
If Cells(i, "C").Value = "" Then
Range("C" & i).Interior.ColorIndex = 6
End If
Next i


MsgBox msg, vbOKOnly, "Error: Some Required Fields are missing."
End Sub

 

[Ron de Bruin]

The event is in the Thisworkbook module and it looks like this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

In the event you can use Cancel = True if you not want to save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("Sheet1").Range("A1").Value = "" Then
MsgBox "Error: Some Required Fields are missing."
Cancel = True
End If
End Sub

 

[Nick Hodge]

You need to do this in the 'ThisWorkbook' module. In the VBE, (Alt+F11)
right click the object 'ThisWorkbook' and select view code. Anything in here
will refer to the workbook as a whole. Now the the top left dropdown in the
main window select the workbook and now in the right dropdown, you will see
all the events you can code against, select BeforeSave and you will get the
template below

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

End Sub

Paste your code in between the Private Sub...End sub portion and it will run
before the user tries to save the workbook. The cancel parameter allows you
to cancel the save conditionally, so for example if you want data in A1 of
sheet1 or else cancel the save (All code un-tested)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Worksheets("Sheet1").Range("A1").Value="" then
Cancel=True
End If
End Sub

The SaveAsUI parameter allows you to detect if the user selects Save or Save
As, so

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI=False then
msgbox "You must use save as... not save"
Cancel=True
End If
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[dmagoo22]

Thanks a bunch. That worked!

You need to do this in the 'ThisWorkbook' module. In the VBE, (Alt+F11)
right click the object 'ThisWorkbook' and select view code. Anything in here
will refer to the workbook as a whole. Now the the top left dropdown in the
main window select the workbook and now in the right dropdown, you will see
all the events you can code against, select BeforeSave and you will get the
template below

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

End Sub

Paste your code in between the Private Sub...End sub portion and it will run
before the user tries to save the workbook. The cancel parameter allows you
to cancel the save conditionally, so for example if you want data in A1 of
sheet1 or else cancel the save (All code un-tested)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Worksheets("Sheet1").Range("A1").Value="" then
Cancel=True
End If
End Sub

The SaveAsUI parameter allows you to detect if the user selects Save or Save
As, so

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI=False then
msgbox "You must use save as... not save"
Cancel=True
End If
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)