beforesave code working sometimes......

[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

 

[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

 

[awrex]

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

 

[Jim Thomlinson]

Check out Chip's site. He covers events along with every other VBA ting under
the sun...

http://www.cpearson.com/excel/topic.htm