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

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 Auto_Open() 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 Auto_Open() in my module.

It works, and I just wanted to share.
--
Len French
Software Development
Lutron Electronics


On Nov 8 1999, 3:00 am, "Nigel Heather"
wrote:
Iwer,

Thanks for your help. As I say in my response to Chip's reply to my
question, yes, my Workbook_Open() code is in ThisWorkbook - but still
doesn't behave as it should. Interestingly though, Auto_Open() does run!

This template started life in Excel 95 and I believe Auto_Open is throwback
to that version. Have I stumbled across some really obscure bug here I
wonder? Or have I got some obscure bug in myVBAcode that is suppressing
the Workbook_Open event onNew?

Thanks for the reply though - at least I have a good way forward until I get
to the root cause of the problem - I'll use Auto_New()!

Nigel.

Iwer Mørck wrote in message

...

Nigel

I tried to put these two sub's into a workbook, saved it as a template and
created anewfile from it. Both msgbox'es fired.

' Standard code module
Sub Auto_Open()
MsgBox "Auto_Open"
End Sub

' ThisWorkbook object
Private Sub Workbook_Open()
MsgBox "Workbook_Open"
End Sub

Could it be that you have put the Workbook_Open into a standard code
module?

--
Cheers

Iwer



Nigel Heather skrev i en
...
Does anyone know if you can put someVBAcode in an Excel 97 template
that
will execute when a user creates anewworkbook from it? I've asked this
question in a previous posting (which was my answer to someone else's
problem) but I hope folks don't mind if I raise it one more time because
I'm
all out of ideas now and facing a delivery deadline.

So far I've discovered that Workbook_Open(), Workbook_SheetActivate()
etc.
don't run. Excel 97 help describes a technique that involves creating an
<snip

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

FWIW: Ordinarily both Workbook_Open() and Auto_Open() 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 Auto_Open.

However there are subtle differences between XL97 and later versions, eg
sometimes the normal Workbook_Open() first then Auto_Open() 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

wrote in message
ups.com...
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 Auto_Open() 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 Auto_Open() in my module.

It works, and I just wanted to share.
--
Len French
Software Development
Lutron Electronics


On Nov 8 1999, 3:00 am, "Nigel Heather"
wrote:
Iwer,

Thanks for your help. As I say in my response to Chip's reply to my
question, yes, my Workbook_Open() code is in ThisWorkbook - but still
doesn't behave as it should. Interestingly though, Auto_Open() does run!

This template started life in Excel 95 and I believe Auto_Open is
throwback
to that version. Have I stumbled across some really obscure bug here I
wonder? Or have I got some obscure bug in myVBAcode that is suppressing
the Workbook_Open event onNew?

Thanks for the reply though - at least I have a good way forward until I
get
to the root cause of the problem - I'll use Auto_New()!

Nigel.

Iwer Mørck wrote in message

...

Nigel

I tried to put these two sub's into a workbook, saved it as a template
and
created anewfile from it. Both msgbox'es fired.

' Standard code module
Sub Auto_Open()
MsgBox "Auto_Open"
End Sub

' ThisWorkbook object
Private Sub Workbook_Open()
MsgBox "Workbook_Open"
End Sub

Could it be that you have put the Workbook_Open into a standard code
module?

--
Cheers

Iwer



Nigel Heather skrev i en
...
Does anyone know if you can put someVBAcode in an Excel 97 template
that
will execute when a user creates anewworkbook from it? I've asked this
question in a previous posting (which was my answer to someone else's
problem) but I hope folks don't mind if I raise it one more time
because
I'm
all out of ideas now and facing a delivery deadline.

So far I've discovered that Workbook_Open(), Workbook_SheetActivate()
etc.
don't run. Excel 97 help describes a technique that involves creating
an
<snip