So you want to give you (as a developer) special dispensation and allow yourself
to save and close the workbook with those cells empty.

You have a few choices.

I'd do this:
Open the VBE
Hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = false
Then back to excel and save/close the workbook.
Then back to the immediate window in the VBE:
application.enableevents = true

The workbook_BeforeSave procedure is one of those events that excel keeps
looking for. By telling it to stop looking at all events, you can do what you
want.

Notice that this isn't difficult to do. Any one who knows a little excel can do
exactly the same thing. So your _BeforeSave event isn't really secure.

=========
If you are only using this workbook as a developer and never want to have this
code stop you from saving, you can add a couple of lines to check to see who's
using the workbook:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range

If Application.UserName = "Your Name Here" Then
Exit Sub
End If

For Each cell In Me.Worksheets("Sheet1").Range("d5,g5,j5,e7,m7,g10" ).Cells
If IsEmpty(cell.Value) Then
MsgBox "You must fill in cell " & cell.Address
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
End Sub

To make sure you spell your username correctly, type this into the immediate
window:
?application.username

And use that name in your code.



wkmooreh wrote:

Ok Dave - Now it works to perfect, I cant save and close it without it
wanting me to fill in the required cells.. What can I do

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range
For Each cell In Me.Worksheets("MASTER").Range("d5,g5,j5,e7,m7,g10" )
If IsEmpty(cell.Value) Then
MsgBox "You must fill in cell " & cell.Address
Application.Goto cell
Cancel = True
Exit For
End If
Next cell
End Sub

--
w

"wkmooreh" wrote:

I need help, I am working on an excel expense report templete and want them
to fill in certain cells before they exit. When I copied the thread from
below I keep getting error messages. I dont know if I am putting valoues of
the cells i want filled out in the wrong place or not. This is what it looks
like: it says for each cell in range line is not right...

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


Dim cell As Range


For Each cell In Range(d5, g5, j5, e7, m7, g10)


If IsEmpty(cell.Value) Then


MsgBox "You must fill in cell " & cell.Address


Application.Goto cell


Cancel = True


Exit For


End If


Next cell


End Sub

--
wkmooreh


"abfabrob" wrote:

I am creating an application form where I want users to fill certain cells in
before closing the document.

Does anyone have an idiots guide to doing this, cos I'm not great with excell.

ANY help welcome!!!

--

Dave Peterson