Workbook_Open ()

[Bill Martin]

I'm using Excel97 and have trouble with Workbook_Open().

Within the module "ThisWorkbook" I have the following three event handler calls:

Option Explicit

Private Sub Workbook_Activate()
Call AddButtons
End Sub

Private Sub Workbook_Deactivate()
Call DeleteButtons
End Sub

Private Sub Workbook_Open()
MsgBox "Workbook_Open Called"
Call AddButtons
End Sub

The Activate and Deactivate events work properly. If I have multiple workbooks
open, whenever this particular workbook is selected from the Window menu, the
code is called and executes as one would expect.

The Workbook_Open event never seems to get called. I open Excel, and then from
the File menu open this workbook, but the code never gets called. Originally it
did not have the MsgBox statement, but I added that for debug and it never gets
called either.

Am I doing something wrong? Is this a known bug in Excel97? It seems like I've
reduced the problem to such a simple example that it *must* work, but it doesn't.

Thanks...

Bill

 

[Norman Jones]

Hi Bill,

You code worked for me 'as is'.

If, however, I introduced a minor spelling error, I could replicate your
experience, e.g.:

Private Sub Workbbook_Open()
MsgBox "Workbook_Open Called"
Call AddButtons
End Sub

 

[Bill Martin]

Changing the code doesn't count. I did a Copy/Paste to put the code here so
that I'd know it was precisely what was in the VBA. I also allowed VBE to
insert the code itself from a menu once rather than me typing it in. I got the
same code and the same problem. Not an error statement of any kind -- it just
doesn't execute.

I'm sort of presuming (hoping?) that somewhere is a security feature of Excel
that allows you to turn off the automatic Workbook_Open event, and which I've
inadvertently set or some such.

Incidentally, can you tell me what version of Excel you tested it with?

Thanks...

Bill
-------------------------

 

[Norman Jones]

Hi Bill,

Changing the code doesn't count. I did a Copy/Paste to put the code here

The spelling error was the only thing that suggested itself.

I'm sort of presuming (hoping?) that somewhere is a security feature of
Excel that allows you to turn off the automatic Workbook_Open event, and
which I've inadvertently set or some such.

I am not aware of any such switch / feature.

Incidentally, can you tell me what version of Excel you tested it with?

xl2k

 

[Dave Peterson]

If that workbook is already open and you use the list at the bottom of the File
dialog, then you're just activating the workbook (if it's not active).

Are you really doing File|Open?

Are you sure that the workbook was actually closed?

 

[Dave Peterson]

If you turn off events, then workbook_open won't fire.

application.enableevents = false
workbooks.open filename:="C:\zzzz.xls"
application.enableevents = true

If you hold down the control key (or the shift key) when you're opening the
workbook, auto_open and workbook_open won't fire.

If you are opening a workbook via a macro and you used a shortcut key for that
macro that included the shift key, then excel gets confused and will not fire
the workbook_open or auto_open.

(I'm still guessing that the workbook was already open when you clicked on the
entry under the File dropdown.)

 

[Bill Martin]

The other two events though, Activate and Deactivate, do work which I *assume*
means that events in general are enabled.

I'm just opening the workbook in the normal way -- mousing down through File >
Open > Select a file. And even if I'm starting Excel cold from the desktop the
Workbook_Open event never works for me.

OK -- I found a new clue. After much putzing around I stumbled into the fact
that if I eliminate my Functions.XLA file, which holds a library of functions
I've written and is an Add-In, then the Workbook_Open() works properly.

I laboriously went in and commented out about half the functions (which aren't
being used anyhow) from the XLA and everything works. So then I start adding
the functions back in one at a time and things become unreproducible. I'll add
back one function and things break again. Take it out again and things are
still broken. Lots more messing about and suddenly things work again with all
the functions re enabled. The only reproducible bit is that removing the XLA
file makes the Open event work.

There's nothing very exciting in any of the small XLA functions. All mundane
stuff, none of which involves events.

Bill

 

[Dave Peterson]

I don't have any guess, but it sounds like you're on the right track--turn
things off, test it. Turn things on, retest--just try to isolate any problem.

Good luck.

 

[Bill Martin]

I solved my problem Dave -- or perhaps circumvented it is more accurate.
Basically I had a function used within my workbook which is called like:

[ ] = MyFunction(Pointer) where Pointer is just a cell location

And buried within the function was a line of the sort:

X = Pointer.Offset(Row,0).Value

Whenever the Row parameter is negative it causes my problems. Whenever it's
positive or zero the Workbook_Open() event works properly. In either case the
function returns the proper value, it just screws up the event with a negative
value.

To add spice to the debug, if I changed the function code it would indeed change
the way the function worked immediately. But it wouldn't change the
Workbook_Open() event issue until I completely exited Excel and restarted it
from the desktop. I must have rebooted Excel 300 times this afternoon.

Anyhow, I reprogrammed to sidestep that problem and all is well within my world
now. I'm still not sure whether that's an Excel bug, or whether I have violated
some programming restriction subtly enough that the function still returns the
right value anyhow. Either way, I'm past it.

Thanks for your help...

Bill

---------------------------------------

 

[Dave Peterson]

Glad you worked it out.

ps. I don't like to use variables that look like VBA
properties/statements/functions. I'd stay away from variables named Row.

I solved my problem Dave -- or perhaps circumvented it is more accurate.
Basically I had a function used within my workbook which is called like:

[ ] = MyFunction(Pointer) where Pointer is just a cell location

And buried within the function was a line of the sort:

X = Pointer.Offset(Row,0).Value

Whenever the Row parameter is negative it causes my problems. Whenever it's
positive or zero the Workbook_Open() event works properly. In either case the
function returns the proper value, it just screws up the event with a negative
value.

To add spice to the debug, if I changed the function code it would indeed change
the way the function worked immediately. But it wouldn't change the
Workbook_Open() event issue until I completely exited Excel and restarted it
from the desktop. I must have rebooted Excel 300 times this afternoon.

Anyhow, I reprogrammed to sidestep that problem and all is well within my world
now. I'm still not sure whether that's an Excel bug, or whether I have violated
some programming restriction subtly enough that the function still returns the
right value anyhow. Either way, I'm past it.

Thanks for your help...

Bill

---------------------------------------

I don't have any guess, but it sounds like you're on the right track--turn
things off, test it. Turn things on, retest--just try to isolate any problem.

Good luck.

 

[andriil]

I have a similar problem.
My Workbook_Open() event doesn't work whenever my workbook contains links to
some other workbook.
When I delete the link, everything seems to work fine.
I tried to replace Open event with Activate event. But I face the same
problem: although Activate event works fine when the workbook is open, update
links prompt seems to intercept it, so that the event is not triggered when
the workbook is opened.
I don't know how to do with it. Can anybody help?
Thanks a lot in advance!

 

[Dave Peterson]

I've never seen this problem.

But could you try moving the code from workbook_open to Auto_open (in a general
module).

Or even move the code to a subroutine named: Continue_open (in that general
module) and use this:

Private Sub Workbook_Open()
Application.OnTime Now, "Continue_Open"
End Sub

Maybe it'll fix some timing issue????

 

[andriil]

Well, I simply used Auto_open in a general module, and happily it worked. But
I think, it's a kind of bug - I mean, events shouldn't be blocked just
because you have some links in your file... By the way, if the file you have
links to is open, id est if the links are updated automatically, events work
well.
Thanks a lot for your help!

 

[Dave Peterson]

It's never happened in anything I've actually seen, but I have seen posts that
say what you did fixes the problem for others.

Glad it worked ok for you.

Well, I simply used Auto_open in a general module, and happily it worked. But
I think, it's a kind of bug - I mean, events shouldn't be blocked just
because you have some links in your file... By the way, if the file you have
links to is open, id est if the links are updated automatically, events work
well.
Thanks a lot for your help!