1

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.

karma
  • 1,999
  • 1
  • 10
  • 14
  • 1
    What is the `test` variable? I can't see that its value has been set anywhere, so its value will be Nothing. Put `Option Explicit` at the top of your code to avoid this, or just write `Range("A1").Value = "test"`. – DS_London Feb 18 '22 at 10:36
  • @DS_London, sorry... typo, what I mean is as you've told me `Range("A1").Value = "test"` (I forgot to add the double quotes). After I add the double quote, yes it did put "test" value in Range("A1"). but still the select command not working and the create new wb not working ? – karma Feb 18 '22 at 10:40
  • 1
    And what do you see if you don't disable `DisplayAlerts`? Does the `SaveAs` return an error? – DS_London Feb 18 '22 at 10:43
  • @DS_London, still the same. After I remove the `DisplayAlerts` code, only the msgbox and the "test" in range("A1") work. The rest gives me nothing. – karma Feb 18 '22 at 10:49
  • 1
    Fwiw, I can replicate this. If you manually save the workbook (using the Menus), the Workbook_BeforeSave() event is fired, the Msg Box appears, AND the new file is created and saved. But, if you call ActiveWorkbook.Save() in code (as the OP is doing), the Msg Box appears (ie the code is being run), but the new workbook does not appear. – DS_London Feb 18 '22 at 11:51
  • 1
    Does the above code creates a new workbook, containing only the two worksheets? Even if not saved. Can you see it being opened (something like "Bookx")... If it exists, it looks that until the event does its job, only the workbook being saved is considered `ActiveWorkbook`. If not, it looks that Excel does not accept creating the new workbook in this event. Which may be considered a bug... – FaneDuru Feb 18 '22 at 11:58
  • @FaneDuru In my test, the new workbook is not created when the Save method is called and fires the event. When the event is fired via manually saving the workbook, a new workbook appears (and becomes the ActiveWorkbook). It does seem as if the Copy() call is simply ignored. – DS_London Feb 18 '22 at 12:01
  • @DS_London This looks a little strange. What Excel version do you use? Microsoft tried increasing what they think being security issues and this can be a matter of something like that. I also must confess that I never tried that on that event. I mean, it may be a working way form the very beginning... – FaneDuru Feb 18 '22 at 12:04
  • 2
    @FaneDuru A good deal of googling suggests this 'feature' has been around for a while. Haven't seen a solution: people just work around by putting the copy/save code into a separate global method and call that from the event handler. Hence other code can call the global method if needed rather than rely on the event handler. I'm on current channel Excel (eg have LAMBDA). – DS_London Feb 18 '22 at 12:12
  • @DS_London Fortunately, I never needed such an approach. That's why I was only expressing (logical) thoughts... – FaneDuru Feb 18 '22 at 12:49
  • @DS_London, yes... you are correct, that if I save the wb (where I run the macro) manually by ctrl+s on the keyboard, it did create the new wb with sheets Data and Tabel, save it to the backup folder then close it. And yes again as you said if the saving process via the code, the msgbox appear but not the create new wb so the backup folder still empty. Thank you for responding my post, DS_London. – karma Feb 19 '22 at 07:46
  • @FaneDuru, it doesn't create the new workbook letalone the wb contains sheet Data and sheet Tabel. As DS_London said, if I save the wb (contains the macro) manually by ctrl+s on the keyboard, it does create the new wb with sheet Data and sheet Tabel and then save this new wb in the backup folder, then close it. I think your opinion in your last sentence is true. Thanks for checking my post, FaneDuru. – karma Feb 19 '22 at 07:47

1 Answers1

2

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.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • Thank you for the answer, Patrick. I'm very sorry as I myself forgot to add the double quote in my code when I run it. What I mean is `.value = "test"`. With the double quote, it did put "test" value in Range("A1"), but still the rest is not give me a result. I've also change all the `activeworkbook` into `thisworkbook`, but still gives me nothing. `ThisWorkbook.Sheets("DATA").Range("A1").Select` also not selecting the cell in the sheet. – karma Feb 18 '22 at 10:46
  • 1
    That why I ALWAYS have `Option Explicit` set by default in the VBE; it would have raised the issue. – iDevlop Feb 18 '22 at 10:51
  • Just now I've tried to put `Option Explicit` at the top of both module, but still the same :(. – karma Feb 18 '22 at 11:03