Workbook_Open fires once only the fails to run again.

[Steve_R]

EDIT: Missed an 'n' in the subject. Should be 'then fails to run again'

I have a WB containing quite a few Subs. Workbook_Open code calls these and was working but it now fails. Trouble-shooting so far:

Copy all sheets and code to a new WB and compile code (no issues).
a) The Workbook_Open Code ran
b) Save and close the workbook, open again. Workbook_Open - failed (tested repeatedly).
c) Move all Workbook_Open Code to it's own module, rename the Sub OpenWB() Make it Public. Create new Workbook_Open() code with a single line OpenWB - failed.
d) insert Msgbox "test" as the first line - failed
e) Delete Workbook_Open Code. Create Workbook the following

Private Sub Workbook_Activate()
MsgBox "OK"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "OK"
End Sub

Both Subs functioned repeatedly

f) Recreate

Private Sub Workbook_Open()
MsgBox "OK"
End Sub

It fails to run.

g) Create a New workbook

insert the code

Private Sub Workbook_Open()
MsgBox "OK"
End Sub

Repeatedly open and close the workbook, the "OK" message appears as it should each time.

h) Restart PC test the workbook created from the original again. It still fails.


Copying sheets and code to a new workbook tested three times for identical results.

Can anyone suggest a reason Workbook_Open may be corrupted after running successfully once only in a workbook?

 

[Logit]

.
Some rambling thoughts here :

So long as you place this macro :

[COLOR=#333333]Private Sub Workbook_Open()
[/COLOR]End Sub

In the ThisWorkbook Module, it should fire each time the workbook is opened.



Copying existing code to a new workbook, in an attempt to correct some unknown error or corruption, may in fact copy the unseen error or corruption to the new workbook.
Although it is tedious, I have found it better to manually enter the existing code in the new workbook (type it in one letter at a time like its the first time you are doing it). That
insures any corruption or unseen error/s does not follow to the new workbook.

"Can anyone suggest a reason Workbook_Open may be corrupted after running successfully once only in a workbook?" No,sorry.

Did you try using the COMPILE command in the VBE form the menu to see if your code compiles successfully ? Just another check.

 

[Steve_R]

.
Some rambling thoughts here :

So long as you place this macro :

[COLOR=#333333]Private Sub Workbook_Open()
[/COLOR]End Sub

In the ThisWorkbook Module, it should fire each time the workbook is opened.

That it doesn't is the reason for the question. That it fails only in one Workbook is causing me confusion and frustration.

Copying existing code to a new workbook, in an attempt to correct some unknown error or corruption, may in fact copy the unseen error or corruption to the new workbook.

Although it is tedious, I have found it better to manually enter the existing code in the new workbook (type it in one letter at a time like its the first time you are doing it). That
insures any corruption or unseen error/s does not follow to the new workbook.

True and tedious alright. I find it hard enough to read all the code, let alone write the lot again but, if all else fails...

Did you try using the COMPILE command in the VBE form the menu to see if your code compiles successfully ? Just another check.

Yes. As soon as everything was copied into each new workbook tested. The only problem seen then was an external link on the first copy. Oddly, using the message option to delete the link caused issues. Next attempt, I closed the message box and deleted the link manually. The Workbook_Open() code ran once. That workbook sheets and code copied to another produced no external link message. The second cloning Workbook_Open code fired once only.

I should add I tried disabling all add-ins but it just struck me that Stephen Bullen's Smart Indenter was not disabled. If it is the problem, logic says the same issue should exist in other workbooks. However, it was written for Excel 2003 and I am using 2010. While it was installed in 2010 and used regularly, perhaps a recent Windows update causes a clash. If so, I will give it up with a lot of regret.

 

[Steve_R]

A blast from the past: I just renamed the Workbook_Open() code that had been moved to a standard module Auto_open()
It works.