Run macro with BeforeSave Event

  • Thread starter Thread starter JDH
  • Start date Start date
J

JDH

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)
ThisWorkbook.Save
mymarcro
End Sub
 
Macro is in "ThisWorkbook"

Private Sub Workbook_BeforeSave()
ThisWorkbook.Save
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.
 
Specifically what error message do you get? RunTime error? Compiler error?
What line of code is highlighted by the debugger when the error occurs?


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
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.
 
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:

ThisWorkbook.Save

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.
 
Back
Top