D
Dave Peterson
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.
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.