PDA

View Full Version : [SOLVED] Workbook_Open operates inconsistenly



dhregan
01-18-2019, 05:00 PM
Hello -

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


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.

Paul_Hossler
01-19-2019, 08:02 AM
CHeck and see if something it setting Application.EnableEvents = False and leaving it that way

Dave
01-19-2019, 08:11 AM
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

dhregan
01-19-2019, 09:32 AM
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?

dhregan
01-19-2019, 09:35 AM
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.

Paul_Hossler
01-19-2019, 09:52 AM
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

dhregan
01-19-2019, 10:12 AM
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.

Fluff
01-19-2019, 10:57 AM
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?

Paul_Hossler
01-19-2019, 01:08 PM
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_Starting_in_Safe_Mode.html

Start, Run excel.exe /s

dhregan
01-22-2019, 09:15 AM
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.

dhregan
01-22-2019, 09:17 AM
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_Starting_in_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.

Fluff
01-22-2019, 09:25 AM
Glad it's sorted & thanks for the feedback