workbook_open stopped working

[MLExcel]

<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--> Hello everybody,


I recently run into a problem with workbook_open.

The code has been successfully running for years.
Now if I open the file nothing happens. Or almost always nothing happens. Sometimes (rarely) it does still work. Unfortunately I cannot reproduce it.

I believe the problem started when I experimented with EnableEvents=True/False in another procedure.

Any help is highly appreciated.
M.



Here is the code:

  Private Sub Workbook_Open()
  Call Sichern
  End Sub
   


  Sub Sichern()
   
      Application.Calculate
   
      antwort = MsgBox("Backup?", vbYesNo, "Backup?")
      If antwort = vbYes Then
   
      'Speichern unter Backup-Dateinamen
      ThisFile = Range("Backup_Pfad").Value

      ActiveWorkbook.SaveAs FileName:=ThisFile, ReadOnlyRecommended:=True
   
      'Speichern unter altem Dateinamen
          'Dabei wird die Datei überschrieben
          'Damit keine Eingabeaufforderung erscheint werden die Alerts ausgeschalten
      Application.DisplayAlerts = False
      ThisFile = Range("Dateipfad").Value

      ActiveWorkbook.SaveAs FileName:=ThisFile, ReadOnlyRecommended:=False
        
      Application.DisplayAlerts = True
      MsgBox "Backup saved!", vbOKOnly
      
      End If
   

  End Sub

There are no other macros in the workbook!
The Sub Sichern itself is working.

The Range("Backup_Pfad") and Range("Dateipfad") refer to cells within the workbook that contain (actually calculate) the filename and location.

Here is a link to the file:
https://1drv.ms/x/s!AlTXI0a7_oHihtNjzUUJzLk5wklAxw

 

[RoryA]

Hey footoo,

Following your comment I tried the following:

I created a mappe1.xlsm file that contains

Private Sub Workbook_Open()
    Application.EnableEvents = True
End Sub

and placed that file in the XLSTART folder.

Now if I open Excel first and then open my file (via explorer) the workbook_open is executed correctly.

However, if I open my file first still nothing happens.

FYI, this will not achieve anything. If events are not enabled, then the code won't run; if they are enabled, there is no need to enable them!

 

[MLExcel]

FYI, this will not achieve anything. If events are not enabled, then the code won't run; if they are enabled, there is no need to enable them!

Well, I understand your reasoning. It should not make a difference. And as wrote in post #9 if I test for Events.enabled they are enabled anyway.

However, putting in the code in a file in XLSTART did make a difference: The workbook_open executes if I start Excel first, but I does not work if I start the file directly from explorer.

 

[RoryA]

The fact remain that that code cannot be the cause since it will not achieve anything.

 

[footoo]

It should not make any difference, but try creating a new file with extension .xlsm instead of .xlsb

 

[MLExcel]

Thanks footoo.
No, that does not solve it.

However I found the following:
If I make a new file (new.xlsm) that only contains the workbook_open code, everything SEAMS to work as it should.

  Private Sub Workbook_Open()
  MsgBox "Test", vbOKOnly, "Test"
  End Sub

At some point I discovered that for the new.xlsm file workbook_open only works if the file (or another one that performs correctly) was opened just seconds before. If I wait 30 seconds before opening the file workbook_open does NOT work!
So there is probably the same happening as when I put the xlsm in XLSTART.

To me it looks a bit like this:

• There is some Excel internal variable set which prevents workbook_open to operate.
• If a “performing” file is opened, it corrects this interal variable.
• If subsequently another “performing” file is opened, workbook_open runs.
• If subsequently the test.xlsb (or my original file) is open it sets the interal variable to the non- performing state.
• If Excel is closed (for more than a few seconds) the variable is also set to the non- performing state.
• From what we tried before we can deduct, that it is not EnableEvents = false

Could it be any setting outside of Excel?

One more thing:
If I delete the column E in the test file (which contains the formulas) the test.xlsb file performs as the new.xlsm file.

The thing that really bothers me is, that a newly created file (new.xlsm) does not perform correctly. This means my Excel is concerned and not just specific files.

Has anybody downloaded the file I provided and tried how it performs on their system?
Here is a different link if anybody want's to try: wetransfer


Best,
MLE

 

[footoo]

When did you last re-boot your computer? Check for viruses?

 

[MLExcel]

When did you last re-boot your computer? Check for viruses?

Rebooting daily. No viruses.

 

[RoryA]

If you open Excel in Safe mode (hold down the Ctrl key while opening it normally) and then open the workbook, does the code run?

 

[MLExcel]

If you open Excel in Safe mode (hold down the Ctrl key while opening it normally) and then open the workbook, does the code run?

Hi Rory!
Thanks for the great tip. Here is the result:
In safe mode it performs more or less the same.
If I open a file where it does not work (like the test.xlsb), it still does not run.
If I start with a file that works (like the new.xlsm created earlier) it works and it also works for the subsequent file - even the test.xlsb that didn’t work when started first.
MLE

 

[RoryA]

I'll try and have a look at your file tomorrow and see if I can replicate the behaviour. Which version of Excel are you using?