Workbook_Open operates inconsistenly

Hello -

I have a simple Workbook_Open macro (in the ThisWorkbook object) as follows:

Code:

---

Private Sub Workbook_Open()

' Ensure that calculations and screen updating are ON
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True


' Leave workbook in working state if last save was in debug mode
    If Range("DEBUG_FLAG").Value = False Then
        Call Set_Defaults
    End If


' Default Migrate Flag to false so that regular processing can commence
    Range("MIGRATE_FLAG").Value = False


End Sub

---

I get inconsistent behavior when opening the workbook. Normally on the first open attempt, the Workbook_Open macro does not run. I know this because the Set_Defaults macro that is called within Workbook_Open does not run. However, on the second attempt, the macro runs. Has anyone seen this before? Thanks in advance for any thoughts on a remedy.

CHeck and see if something it setting Application.EnableEvents = False and leaving it that way

U haven't identified what sheet the range relates to? U also haven't indicated what the set defaults macro does or what happens on workbook close and/or save? The Range("DEBUG_FLAG").Value is not a Boolean (which would be auto false on wb open) so chances are it equals vbnullstring on wb open until set to false. HTH. Dave

Quote:

---

Originally Posted by Paul_Hossler

CHeck and see if something it setting Application.EnableEvents = False and leaving it that way

---

Thank you, Paul. But when you first open a workbook, isn't Application.EnableEvents = True always, or does it retain the setting from when last saved?

Quote:

---

Originally Posted by Dave

U haven't identified what sheet the range relates to? U also haven't indicated what the set defaults macro does or what happens on workbook close and/or save? The Range("DEBUG_FLAG").Value is not a Boolean (which would be auto false on wb open) so chances are it equals vbnullstring on wb open until set to false. HTH. Dave

---

Thank you, Dave. But I'm still not following why the code executes when I open the workbook the second time, without having made any changes or saving the workbook when I immediately exit the first opening of the workbook. FWIW, Set_Defaults pre-fills some cells based on values from the last save, and Range("DEBUG_FLAG") contains a boolean value.

Quote:

---

Originally Posted by dhregan

Thank you, Paul. But when you first open a workbook, isn't Application.EnableEvents = True always, or does it retain the setting from when last saved?

---

When Excel opens, Application.EnableEvents = True, but if

1. WB #1 sets Application.EnableEvents = False

2. WB#1 is closed, but Excel is not

3. WB#2 opened (e.g. File, Open) then the Application.EnableEvents status is persistent

Example:

a. Open Excel

b. File Open WB1.xlsm - WB Open files with message, and turns EnableEvents off

c. File Close WB1.xlsm, but leave Excel Open
.

d. File Open WB2.xlsm - WB Open event does not file

e. Close Excel

f. Open Excel and Open WB2 - WB Open fires


I always turn off EnableEvent, do only what might cause an event handler to be called, and turn it on again -- usually within 2-3 lines, but always in the same sub

Quote:

---

Originally Posted by Paul_Hossler

When Excel opens, Application.EnableEvents = True, but if

1. WB #1 sets Application.EnableEvents = False

2. WB#1 is closed, but Excel is not

3. WB#2 opened (e.g. File, Open) then the Application.EnableEvents status is persistent

Example:

a. Open Excel

b. File Open WB1.xlsm - WB Open files with message, and turns EnableEvents off

c. File Close WB1.xlsm, but leave Excel Open
.

d. File Open WB2.xlsm - WB Open event does not file

e. Close Excel

f. Open Excel and Open WB2 - WB Open fires


I always turn off EnableEvent, do only what might cause an event handler to be called, and turn it on again -- usually within 2-3 lines, but always in the same sub

---

Thanks again, Paul. I should have clarified in my original post that between open attempt #1 and open attempt #2, I am closing Excel entirely without saving the file.

If you put the word Stop at the very beginning of your open event.
Then close & open the workbook as few times, does the VBE open & highlight the line everytime you open the workbook?

Quote:

---

Originally Posted by dhregan

Thanks again, Paul. I should have clarified in my original post that between open attempt #1 and open attempt #2, I am closing Excel entirely without saving the file.

---

Are there any add-ins, Personal.xlsm, or other hidden workbooks (XLSTART folder) that might be affecting it?


Try Excel safe mode

https://excelribbon.tips.net/T011632...Safe_Mode.html

Start, Run excel.exe /s

Quote:

---

Originally Posted by Fluff

If you put the word Stop at the very beginning of your open event.
Then close & open the workbook as few times, does the VBE open & highlight the line everytime you open the workbook?

---

Hello Fluff -

I don't know what changed, but after looking into the items that Paul noted in post 9, the problem appears to have gone away. Nonetheless, I inserted the Stop statement and was able to verify that the Workbook_Open macro is firing. Thanks for your suggestion.

Quote:

---

Originally Posted by Paul_Hossler

Are there any add-ins, Personal.xlsm, or other hidden workbooks (XLSTART folder) that might be affecting it?


Try Excel safe mode

https://excelribbon.tips.net/T011632...Safe_Mode.html

Start, Run excel.exe /s

---

Hello Paul -

I confirmed that there are no active add-ins, there is no personal.xlsm and that XLSTART is empty. I also opened the workbook in safe mode. After all of these things, the problem seems to have disappeared. I didn't change anything, so I don't know what made the problem go away. Thanks for your suggestions.

Glad it's sorted & thanks for the feedback