BeforeSave workbook event

  • Thread starter Thread starter Cindy
  • Start date Start date
C

Cindy

Hi

I have code that runs on the workbook BeforeSave event.
How do I "cancel" the actual save process here? Depending
on what the code returns, I want the save process
cancelled and the workbook left on the screen.

Thanks

Cindy
 
Thanks Ron

It works. Let me ask you another question - is there a
way to have the BeforeSave and BeforeClose code NOT run?
What my code does it check that all required data is
filled-in. Now, in the design phase, I need to save
design changes and code but the BeforeSave and BeforeClose
code runs and won't allow me to save without filling in
required data. Any ideas?

Thanks

Cindy
 
Hi Cindy

Right click on the excel icon next to file on the menubar
choose view code

The ThisWorkbook is now active
paste the code there

Every time you press the save(or ctrl-s) button the Workbook_BeforeSave is
run and check the cells for you.
If not all the cells have a value you can't save


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myrange As Range
Set myrange = Worksheets("Sheet1").Range("A1:A6")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
Cancel = True
End If
End Sub
 
Hi Ron

I'm a little confused. My code is in the BeforeSave
event. What my problem is I can't save my design changes
because the "BeforeSave" codes runs and want's data filled-
in and I need to start with a "blank" form - no data.

Cindy
 
Sorry I have not understand you
I am a Stupid Dutch guy<g>

You can use this to disable all the events

Application.EnableEvents = False

restore it with

Application.EnableEvents = true
 
Where would I put this code?

Since it's a "template", I can't get the "on open" code to
run because the user is not actually opening it, but
calling on the template to create a new file.

And where would I put the EnableEvents = False when I only
want to disable the events to save my changes?

Sorry this is getting too complicated. I appreciate all
your help.

Cindy
 
If you use it in a template you can use this as first line in the event

If Len(ThisWorkbook.Path) = 0 Then Exit Sub

If the template is not saved the path length = 0
 
Ron,

I still don't know where to put the code to trigger it to
run when the user does "file-new-selects the template".

Thanks,

Cindy
 
What event?
-----Original Message-----
If you use it in a template you can use this as first line in the event

If Len(ThisWorkbook.Path) = 0 Then Exit Sub

If the template is not saved the path length = 0

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)







.
Click to expand...
 
Hi cindy

In all the events that you use this as first line

If Len(ThisWorkbook.Path) = 0 Then Exit Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Len(ThisWorkbook.Path) = 0 Then Exit Sub

' your code

End Sub


If you open the template the events will not work because the length
of the ThisWorkbook.Path = 0

As soon as you save the template as a excel file the events are working because
you have a path (C:\test.xls or so)
 
This won't work for what I need to do because the path
length will be 0 because they won't save the file until
the data is verified and so the code on BeforeSave and
BeforeClose will never run.

What I really need & can't figure out is a way to run code
when the template is being used because then I could
disableevents while in design mode, save the template and
close. When the user "uses" the template, the first code
that will run is "enableevents". The problem is I can't
figure out how to run this code when the template is
being "used". The "open" event doesn't work.

I'm sorry to take up your time and appreciate your help,
but if you don't have the time, don't worry about it.
THanks

Cindy
 
Ron,

I went back to "old" technology and created a sub
procedure named auto_open and enabled the events here.
This runs when the user "uses" the template file. Thanks
for all your help!

Cindy
 
Ron,

I went back to "old" technology and created a sub
procedure named auto_open and enabled the events here.
This runs when the user "uses" the template file. Thanks
for all your help!

Cindy
 
Back
Top