Trying to run macro on the BeforeSave event

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

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" <dennis.mcgowan@lacek.com> wrote in message news:1152128546.095187.174890@m79g2000cwm.googlegroups.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
>

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
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"dmagoo22" <dennis.mcgowan@lacek.com> wrote in message
news:1152128546.095187.174890@m79g2000cwm.googlegroups.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
>

Thanks a bunch. That worked!


Nick Hodge wrote:
> 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
> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>
>
> "dmagoo22" <dennis.mcgowan@lacek.com> wrote in message
> news:1152128546.095187.174890@m79g2000cwm.googlegroups.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
> >