Run macro with BeforeSave Event



Hi again,

I have a macro that I am running on a BeforeClose event and I need it to run
on a Workbook_BeforeSave event. When I click Save I receive an error message.
As of now I having to use:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub


Macro is in "ThisWorkbook"

Private Sub Workbook_BeforeSave()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Mold Release Verification\QSA Mold Release Verification.htm",
FileFormat _
:=xlHtml, ReadOnlyRecommended:=True, CreateBackup:=False
Application.DisplayAlerts = True
End Sub

Error message: Compile error - Precedure declaration dose not match or
procedure having the same name.

Chip Pearson

Specifically what error message do you get? RunTime error? Compiler error?
What line of code is highlighted by the debugger when the error occurs?

Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
(email on web site)

Barb Reinhardt

The first line needs to look like this

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

And you need to do some other checking, but I don't have time to help with
that part. I've got a fire of my own to put out. :) Hopefully someone
else can assist.


It is in "ThisWorkbook" module

When I use: "Private Sub Workbook_BeforeSave()"

Error message: Compile error - Precedure declaration dose not match or
procedure having the same name.

When I use: "Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)"

I receive a message "Microsoft Excel for windows has encountered a problem
and needs to close.

Barb Reinhardt

When I step through your code, it appears to go into an infinite loop. Every
time you save, it goes back through the Workbook_BeforeSave event. You may
need to add

Application.Enableevents = FALSE

at the beginning of your code and

Application.Enableevents = TRUE

at the end of your code


Just looking at the code real quick, it looks to me that the first line in
the sub is going to cause a problem:


This event is being called because the file is about to be saved, so after
it runs the file will save and that line is unnecessary. I would also think,
and I could be wrong, but if before it saves it runs this sub, when you try
to save it, it wants to run through it again.

Just my quick thoughts. Hope it helps.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question