CODEWITHSUNDEEP
excelvbaexcel-2010
karma
karma

Reputation: 2009

Excel VBA, ActiveWorkbook.Save trigger the event Workbook_BeforeSave, but the code line in Workbook_BeforeSave is passed without any result

I've searched this site and found similar problem but still different.
Those I found in this site (such as this, this and this) involve too much code (which maybe the reason it doesn't do correctly) and also involve Workbook_BeforeClose event.

Mine is very simple as follow :

Sub RefreshData()
ActiveWorkbook.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
MsgBox "test"
Range("A1").Select
Range("A3").Value = "test"
Sheets(Array("TABEL", "DATA")).Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path + "\Backup\blablabla " & Format(Now(), "yymmdd hh mm ss") & ".xlsx", FileFormat:=51
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

When I run the RefreshData sub, it trigger to Workbook_BeforeSave sub.

enter image description here

Within the code in Workbook_BeforeSave, when the yellow line pass the msgbox code it show the result as Excel shows the message box. But when the yellow line pass the rest of the code, it does nothing, no error. Range("A1") not selected but it does have the "test" value. New workbook with sheets DATA and TABEL not created, letalone is saved to the backup folder and close it. Yet, the workbook with the macro is saved.

My question : is that normal ?

My hope : someone will be kind enough to replicate the code above in a new workbook in his Excel app and run it. If all the codes in Workbook_BeforeSave work accordingly, then there's something wrong with my Excel app and I hope someone can inform me what might be the cause within my Excel app.

Any kind of help would be greatly appreciated.
Thank you in advanced.

Upvotes: 1

Views: 1181

Answers (1)

iDevlop
iDevlop

Reputation: 25272

More a suggestion than an answer, but: Try properly qualifying ranges:
change Range("A1").Value = test
into ThisWorkbook.Range("A1").Value = test

Also if it's about ThisWorkbook, use that instead of ActiveWorkbook.

Upvotes: 2

Related Questions