Excel 97 fails to execute Workbook_Open() on New XLS from Template (FYI)

[ljfrench]

Wow, a really old post that helped. I just wanted to share my
experience with this issue.

I write VBA to create interactive forms. I use a combobox for choices
to create a schedule or list. This combobox populated with data in the
Workbook_Open() method. It worked fine for a year or two.

I don't know how I broke it, but my latest release failed during
testing because the combobox wouldn't populate. The conditions were
specific: it only broke when creating a new spreadsheet from the
template and only on Excel 97.

I created the Autpen() sub as described below (in a module) as well
as an Initialize_Sheet() function (it could be any name). I call the
Initialize_Sheet() method from both Workbook_Open() in ThisWorkbook
and Autpen() in my module.

It works, and I just wanted to share.

 

[Peter T]

FWIW: Ordinarily both Workbook_Open() and Autpen() run when template is
opened in Excel 97 (for me).

There are certain scenarios in which neither one nor the other or neither
will run (all versions). A common reason for the Workbook_Open() event not
firing is if EnableEvents is disabled. If opening a file programmatically
need to do RunAutoMacros to fire the Autpen.

However there are subtle differences between XL97 and later versions, eg
sometimes the normal Workbook_Open() first then Autpen() order is
reversed.

If you want to be ultra sure of catching either one but not both, in each
exit if a global flag (eg gbAppRunning) is true else set the flag and run
the code.

Regards,
Peter T

Wow, a really old post that helped. I just wanted to share my
experience with this issue.

I write VBA to create interactive forms. I use a combobox for choices
to create a schedule or list. This combobox populated with data in the
Workbook_Open() method. It worked fine for a year or two.

I don't know how I broke it, but my latest release failed during
testing because the combobox wouldn't populate. The conditions were
specific: it only broke when creating a new spreadsheet from the
template and only on Excel 97.

I created the Autpen() sub as described below (in a module) as well
as an Initialize_Sheet() function (it could be any name). I call the
Initialize_Sheet() method from both Workbook_Open() in ThisWorkbook
and Autpen() in my module.

It works, and I just wanted to share.