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

 

[footoo]

What files do you have in XLSTART?
Do any of them have code that is setting EnableEvents to false?

 

[MLExcel]

What files do you have in XLSTART?
Do any of them have code that is setting EnableEvents to false?

There are no files in XLSTART.

 

[MLExcel]

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.

 

[gallen]

I would put a stop in the workbook open to make sure the code is being called at all (ruling out code error)

[COLOR=#333333]Private Sub Workbook_Open()
[/COLOR]Stop 'Testing purposes only
[COLOR=#333333]Call Sichern[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

If the code interrupts there then you know at least it is being called.

If it doesn't first ensure that application.EnableEvents is true by typing in the immediate window:

?Applcation.EnableEvents

and hitting enter


if it still doesn't in can only be:

1. Security settings are preventing code execution
2. You have excel open elsewhere and that has control of code execution.

The fact you said it only started when you were playing around with EnableEvents would definitely point to that as prime suspect.

When using EnableEvents always ensure it is re-enabled especially on errors.

 

[gallen]

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.

Well that at least confirms the error. But you need to find where is it being disabled? Do a search in all your code in all workbooks with macros and look for "Application.EnableEvents = False" then ensure that a subsequent re-enable is being called.

 

[MLExcel]

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.

One more addendum:

For the original file (the provided test.xlsb is a file including only the vba and the worksheet containing the ranges) this does not work.
The VBA for both files is exactly the same!

 

[footoo]

Well that at least confirms the error. But you need to find where is it being disabled? Do a search in all your code in all workbooks with macros and look for "Application.EnableEvents = False" then ensure that a subsequent re-enable is being called.

When Excel is closed, EnableEvents is automatically set to true.
So I can't think why the workbook_open is not being triggered when the file is being opened in a new Excel launch.

Check whether there are any hidden files that have been opened (View/Window/Unhide)

 

[MLExcel]

I would put a stop in the workbook open to make sure the code is being called at all (ruling out code error)

[COLOR=#333333]Private Sub Workbook_Open()
[/COLOR]Stop 'Testing purposes only
[COLOR=#333333]Call Sichern[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

If the code interrupts there then you know at least it is being called.

Same as above: If I first start Excel (with the xlsm in XLSTART), the test - file calls the code correctly. The original file does not.

If it doesn't first ensure that application.EnableEvents is true by typing in the immediate window:

?Applcation.EnableEvents

and hitting enter

This is interessting:
If I open the test file directly. The code is not called (see above). But if I then test ?Application.EnableEvents in the immediate window it is TRUE!

I now tried the following:
I removed the xlsm file from XLSTART.
If I start Excel and test for ?Application.EnableEvents it is still TRUE!

If I now open the test file
First (without excel already running) the code does NOT execute.
If I start excel and then the test file from the explorer the code does NOT execute.
If I start excel and then the test file from Excel toolbar the code DOES execute ONCE. If I repeat that it does not work again.

if it still doesn't in can only be:

1. Security settings are preventing code execution
2. You have excel open elsewhere and that has control of code execution.

1. should not be the case, as the code runs sometimes or if started manually
2. not that I would be aware of. Can I test for this?

 

[MLExcel]

Check whether there are any hidden files that have been opened (View/Window/Unhide)

No, that's not case either.