beforesave code working sometimes......

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
THANK YOU!!!!!
Works great. Would you happen to know of web reference that I can look at
regarding this.
 
Back
Top