beforesave code working sometimes......

A

awrex

Ok I had posted a question about some code (Subject: Code issue -
before_save() - Filename with date stamp - worksheet), to which I got it to
work but now it is causing my Excel to completely crash.

Any ideas??? Also Why do I get 2 vbok pop up boxes????

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


MsgBox "You should see something renamed!!", vbOK

Sheet1.Name = "As of " & Format(Now(), "MM-DD-YYYY")

ChDir "<new dir path>"
ActiveWorkbook.SaveAs _
"<MyFileName>" & Format(Now(), "yyyymmdd")

End Sub
 
J

Jim Thomlinson

Your save as is calling a save which is going to be an issue. You need to
cancel the pending save make your changes disable events, save the book and
then re enable... something like this...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On error goto ErrorHandler
Application.enableevents = false
cancel = true
MsgBox "You should see something renamed!!", vbOK

Sheet1.Name = "As of " & Format(Now(), "MM-DD-YYYY")

ChDir "<new dir path>"
ActiveWorkbook.SaveAs _
"<MyFileName>" & Format(Now(), "yyyymmdd")
ErrorHandler:
Application.enableevents = true
End Sub
 
A

awrex

THANK YOU!!!!!
Works great. Would you happen to know of web reference that I can look at
regarding this.
 

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

Top