Using Office 2003 and Windows XP;

I have the following event in ThisWorkbook:

Private Sub Workbook_Open()
Call FileOpenRequest
End Sub

In the above, FileOpenRequest is a function that first unhides a sheet and
very hides a couple others, turns on sheet protection without any password,
and a few other pretty basic settings so the opening sheet will look right
for the user.

The first time I open this file Workbook_Open runs perfectly. The second
time I open the file in the same instance of Excel, Workbook_Open fails to
fire. It is as though macros are disabled, even though they are not. Security
is set to medium and it asks if macros should be enabled, of course I open
with macros enabled.

After the file loads, I can open the VBE and manually run the code and it
runs fine. If I close Excel completely and open a new instance, it runs fine
the first time, then it will never fire again in that instance.

Does anyone have a clue why it is failing like this? And, more importantly,
how the heck to fix it?

Thanks much in advance for your assistance.

Add a message box to the code below. When the workbook opens and the box
appears you can type CNTL Break to debug the code.


Private Sub Workbook_Open()
msgbox("opened workbook")
Call FileOpenRequest
End Sub


"XP" wrote:

Using Office 2003 and Windows XP;

I have the following event in ThisWorkbook:

Private Sub Workbook_Open()
Call FileOpenRequest
End Sub

In the above, FileOpenRequest is a function that first unhides a sheet and
very hides a couple others, turns on sheet protection without any password,
and a few other pretty basic settings so the opening sheet will look right
for the user.

The first time I open this file Workbook_Open runs perfectly. The second
time I open the file in the same instance of Excel, Workbook_Open fails to
fire. It is as though macros are disabled, even though they are not. Security
is set to medium and it asks if macros should be enabled, of course I open
with macros enabled.

After the file loads, I can open the VBE and manually run the code and it
runs fine. If I close Excel completely and open a new instance, it runs fine
the first time, then it will never fire again in that instance.

Does anyone have a clue why it is failing like this? And, more importantly,
how the heck to fix it?

Thanks much in advance for your assistance.

Okay, Joel. I think I discovered what is happening.

I have:

Application.EnableEvents = False

Nothing will run after the line "ThisWorkbook.Close" because when the
workbook is closed the macro stops running.

The code below is better, but if the Save fails the events are not enabled

ThisWorkbook.Save
Application.EnableEvents = True
ThisWorkbook.Close

This is even better

On Error go to finish
ThisWorkbook.Save
Application.EnableEvents = True
ThisWorkbook.Close
exit sub
finish:msgbox("Error: Failed to Save Thisworkbook")
Application.EnableEvents = True
end sub


"XP" wrote:

Okay, Joel. I think I discovered what is happening.

I have:

Application.EnableEvents = False
.
.
<some other code here
.
ThisWorkbook.Save: ThisWorkbook.Close
Application.EnableEvents = True

But, I bet once the file is saved and closed, the line to enable events
doesn't take, thereby killing the macros on the next open. Am I right?

How can I trap and control a close like this? I need the code to display a
blank sheet on the way out in case the file is opened with macros disabled,
etc.

BTW, thanks for your help.


"Joel" wrote:

Add a message box to the code below. When the workbook opens and the box
appears you can type CNTL Break to debug the code.


Private Sub Workbook_Open()
msgbox("opened workbook")
Call FileOpenRequest
End Sub


"XP" wrote:

Using Office 2003 and Windows XP;

I have the following event in ThisWorkbook:

Private Sub Workbook_Open()
Call FileOpenRequest
End Sub

In the above, FileOpenRequest is a function that first unhides a sheet and
very hides a couple others, turns on sheet protection without any password,
and a few other pretty basic settings so the opening sheet will look right
for the user.

The first time I open this file Workbook_Open runs perfectly. The second
time I open the file in the same instance of Excel, Workbook_Open fails to
fire. It is as though macros are disabled, even though they are not. Security
is set to medium and it asks if macros should be enabled, of course I open
with macros enabled.

After the file loads, I can open the VBE and manually run the code and it
runs fine. If I close Excel completely and open a new instance, it runs fine
the first time, then it will never fire again in that instance.

Does anyone have a clue why it is failing like this? And, more importantly,
how the heck to fix it?

Thanks much in advance for your assistance.