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 wrote:
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 wrote:

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 wrote:

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 wrote:


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 wrote:


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

---
Regards,
Norman



"Bill Martin" wrote in message
. ..



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