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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"dmagoo22" wrote in message oups.com...
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