Workbook_Open() Event
 

Is there any trick to getting the Workbook_Open() event to work? I currently
have a workbook with the following calls in the ThisWorkbook code module:

Option Explicit
Private Sub Workbook_Activate()
Call AddButtons
End Sub

Private Sub Workbook_Deactivate()
Call DeleteButtons
End Sub

Private Sub Workbook_Open()
Call AddButtons
End Sub

It compiles properly and works flawlessly as I switch back and forth between
workbooks, but it never seems to work when I first open a workbook. I can open
two different workbooks that have this code and get no buttons created. Then I
switch between them and the buttons come and go as expected. I'm using Excel97
if that makes any difference.

I've also tried immediately disabling events upon entering Workbook_Open, then
executing the Call and finally enabling events again before exiting but it makes
no difference. It just seems like the event never triggers. Or perhaps I'm
misunderstanding what it should do?

Thanks.

Bill

Workbook_Open() Event
 

How are you opening the workbook?

File|open or double clicking on its filename in Windows Explorer?

Or are you opening the workbook using code in another workbook or a shortcut key
that includes the shift button?

If you add:

Private Sub Workbook_Open()
msgbox "hi from workbook_open"
Call AddButtons
End Sub

do you see the msgbox (Maybe it's a problem with addbuttons???).

========
If worse came to worse, maybe just moving that workbook_open procedure to a
general module and renaming it to Auto_open() would be a workaround???

Bill Martin wrote:

Is there any trick to getting the Workbook_Open() event to work? I currently
have a workbook with the following calls in the ThisWorkbook code module:

Option Explicit
Private Sub Workbook_Activate()
Call AddButtons
End Sub

Private Sub Workbook_Deactivate()
Call DeleteButtons
End Sub

Private Sub Workbook_Open()
Call AddButtons
End Sub

It compiles properly and works flawlessly as I switch back and forth between
workbooks, but it never seems to work when I first open a workbook. I can open
two different workbooks that have this code and get no buttons created. Then I
switch between them and the buttons come and go as expected. I'm using Excel97
if that makes any difference.

I've also tried immediately disabling events upon entering Workbook_Open, then
executing the Call and finally enabling events again before exiting but it makes
no difference. It just seems like the event never triggers. Or perhaps I'm
misunderstanding what it should do?

Thanks.

Bill

--

Dave Peterson

Workbook_Open() Event
 

It does not seem to matter how I open it Dave. To verify I just tried all ways
and always get the same failure. Adding the debug message does not help. That
code never executes.

After mulling it over and putzing with various things, I started stripping stuff
out of the workbook -- about 200 sheets deleted and a couple hours later I
finally found out what's going on, though I still don't understand it.

I have a custom function which I use to look at a column of randomly organized
numbers and return the average of the largest "n" of the numbers - "n" being
specified on another sheet. This function is used once on each of a couple
hundred sheets.

Anyhow, if I delete that function from all sheets then when the workbook opens
the event will fire properly. Then I modified the custom function to just
immediately return a value =1 and exit. The event still fails that way though.

My immediate kludge concept is to replace the function with a macro that I'll
somehow force to execute (200 times) before I actually use the result from that
cell. I'm certainly open to a better approach though if you have one to
suggest. Or I may spend tomorrow dinking around with the function to see if I
can figure what's strange about it although all it does now is return =1.

Thanks...

Bill
-------------------------------
Dave Peterson wrote:
How are you opening the workbook?

File|open or double clicking on its filename in Windows Explorer?

Or are you opening the workbook using code in another workbook or a shortcut key
that includes the shift button?

If you add:

Private Sub Workbook_Open()
msgbox "hi from workbook_open"
Call AddButtons
End Sub

do you see the msgbox (Maybe it's a problem with addbuttons???).

========
If worse came to worse, maybe just moving that workbook_open procedure to a
general module and renaming it to Auto_open() would be a workaround???

Bill Martin wrote:

Is there any trick to getting the Workbook_Open() event to work? I currently
have a workbook with the following calls in the ThisWorkbook code module:

Option Explicit
Private Sub Workbook_Activate()
Call AddButtons
End Sub

Private Sub Workbook_Deactivate()
Call DeleteButtons
End Sub

Private Sub Workbook_Open()
Call AddButtons
End Sub

It compiles properly and works flawlessly as I switch back and forth between
workbooks, but it never seems to work when I first open a workbook. I can open
two different workbooks that have this code and get no buttons created. Then I
switch between them and the buttons come and go as expected. I'm using Excel97
if that makes any difference.

I've also tried immediately disabling events upon entering Workbook_Open, then
executing the Call and finally enabling events again before exiting but it makes
no difference. It just seems like the event never triggers. Or perhaps I'm
misunderstanding what it should do?

Thanks.

Bill

Workbook_Open() Event
 

Is there any chance that you have an error in the UDF? If there is, maybe it's
screwing other things up???

Did you try putting the code into Auto_Open() (in a general module)?

And I've seen people recommend code like this when there seems to be a timing
issue:

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

Then in a general module:

sub Continue_Open()
'your real code here
call addbuttons
end sub

But all these are just guesses.

Good luck.


Bill Martin wrote:

It does not seem to matter how I open it Dave. To verify I just tried all ways
and always get the same failure. Adding the debug message does not help. That
code never executes.

After mulling it over and putzing with various things, I started stripping stuff
out of the workbook -- about 200 sheets deleted and a couple hours later I
finally found out what's going on, though I still don't understand it.

I have a custom function which I use to look at a column of randomly organized
numbers and return the average of the largest "n" of the numbers - "n" being
specified on another sheet. This function is used once on each of a couple
hundred sheets.

Anyhow, if I delete that function from all sheets then when the workbook opens
the event will fire properly. Then I modified the custom function to just
immediately return a value =1 and exit. The event still fails that way though.

My immediate kludge concept is to replace the function with a macro that I'll
somehow force to execute (200 times) before I actually use the result from that
cell. I'm certainly open to a better approach though if you have one to
suggest. Or I may spend tomorrow dinking around with the function to see if I
can figure what's strange about it although all it does now is return =1.

Thanks...

Bill
-------------------------------
Dave Peterson wrote:
How are you opening the workbook?

File|open or double clicking on its filename in Windows Explorer?

Or are you opening the workbook using code in another workbook or a shortcut key
that includes the shift button?

If you add:

Private Sub Workbook_Open()
msgbox "hi from workbook_open"
Call AddButtons
End Sub

do you see the msgbox (Maybe it's a problem with addbuttons???).

========
If worse came to worse, maybe just moving that workbook_open procedure to a
general module and renaming it to Auto_open() would be a workaround???

Bill Martin wrote:

Is there any trick to getting the Workbook_Open() event to work? I currently
have a workbook with the following calls in the ThisWorkbook code module:

Option Explicit
Private Sub Workbook_Activate()
Call AddButtons
End Sub

Private Sub Workbook_Deactivate()
Call DeleteButtons
End Sub

Private Sub Workbook_Open()
Call AddButtons
End Sub

It compiles properly and works flawlessly as I switch back and forth between
workbooks, but it never seems to work when I first open a workbook. I can open
two different workbooks that have this code and get no buttons created. Then I
switch between them and the buttons come and go as expected. I'm using Excel97
if that makes any difference.

I've also tried immediately disabling events upon entering Workbook_Open, then
executing the Call and finally enabling events again before exiting but it makes
no difference. It just seems like the event never triggers. Or perhaps I'm
misunderstanding what it should do?

Thanks.

Bill



--

Dave Peterson

Workbook_Open() Event
 

Pardon my being dense, but what is a UDF? I looked in the Excel help system and
both of Walkenbach's books but I don't find that term - not to say that it isn't
in there somewhere, but it's not indexed anyhow.

Bill
----------------------------
Dave Peterson wrote:
Is there any chance that you have an error in the UDF? If there is, maybe it's
screwing other things up???

Did you try putting the code into Auto_Open() (in a general module)?

And I've seen people recommend code like this when there seems to be a timing
issue:

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

Then in a general module:

sub Continue_Open()
'your real code here
call addbuttons
end sub

But all these are just guesses.

Good luck.


Bill Martin wrote:

It does not seem to matter how I open it Dave. To verify I just tried all ways
and always get the same failure. Adding the debug message does not help. That
code never executes.

After mulling it over and putzing with various things, I started stripping stuff
out of the workbook -- about 200 sheets deleted and a couple hours later I
finally found out what's going on, though I still don't understand it.

I have a custom function which I use to look at a column of randomly organized
numbers and return the average of the largest "n" of the numbers - "n" being
specified on another sheet. This function is used once on each of a couple
hundred sheets.

Anyhow, if I delete that function from all sheets then when the workbook opens
the event will fire properly. Then I modified the custom function to just
immediately return a value =1 and exit. The event still fails that way though.

My immediate kludge concept is to replace the function with a macro that I'll
somehow force to execute (200 times) before I actually use the result from that
cell. I'm certainly open to a better approach though if you have one to
suggest. Or I may spend tomorrow dinking around with the function to see if I
can figure what's strange about it although all it does now is return =1.

Thanks...

Bill
-------------------------------
Dave Peterson wrote:

How are you opening the workbook?

File|open or double clicking on its filename in Windows Explorer?

Or are you opening the workbook using code in another workbook or a shortcut key
that includes the shift button?

If you add:

Private Sub Workbook_Open()
msgbox "hi from workbook_open"
Call AddButtons
End Sub

do you see the msgbox (Maybe it's a problem with addbuttons???).

========
If worse came to worse, maybe just moving that workbook_open procedure to a
general module and renaming it to Auto_open() would be a workaround???

Bill Martin wrote:


Is there any trick to getting the Workbook_Open() event to work? I currently
have a workbook with the following calls in the ThisWorkbook code module:

Option Explicit
Private Sub Workbook_Activate()
Call AddButtons
End Sub

Private Sub Workbook_Deactivate()
Call DeleteButtons
End Sub

Private Sub Workbook_Open()
Call AddButtons
End Sub

It compiles properly and works flawlessly as I switch back and forth between
workbooks, but it never seems to work when I first open a workbook. I can open
two different workbooks that have this code and get no buttons created. Then I
switch between them and the buttons come and go as expected. I'm using Excel97
if that makes any difference.

I've also tried immediately disabling events upon entering Workbook_Open, then
executing the Call and finally enabling events again before exiting but it makes
no difference. It just seems like the event never triggers. Or perhaps I'm
misunderstanding what it should do?

Thanks.

Bill

Workbook_Open() Event
 

Oops. I just realized UDF = User Defined Function. I'll look into your other
recommendations and see what happens.

Thanks...

Bill
-----------------------------
Bill Martin wrote:
Pardon my being dense, but what is a UDF? I looked in the Excel help system and
both of Walkenbach's books but I don't find that term - not to say that it isn't
in there somewhere, but it's not indexed anyhow.

Bill
----------------------------
Dave Peterson wrote:

Is there any chance that you have an error in the UDF? If there is, maybe it's
screwing other things up???

Did you try putting the code into Auto_Open() (in a general module)?

And I've seen people recommend code like this when there seems to be a timing
issue:

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

Then in a general module:

sub Continue_Open()
'your real code here
call addbuttons
end sub

But all these are just guesses.

Good luck.


Bill Martin wrote:


It does not seem to matter how I open it Dave. To verify I just tried all ways
and always get the same failure. Adding the debug message does not help. That
code never executes.

After mulling it over and putzing with various things, I started stripping stuff
out of the workbook -- about 200 sheets deleted and a couple hours later I
finally found out what's going on, though I still don't understand it.

I have a custom function which I use to look at a column of randomly organized
numbers and return the average of the largest "n" of the numbers - "n" being
specified on another sheet. This function is used once on each of a couple
hundred sheets.

Anyhow, if I delete that function from all sheets then when the workbook opens
the event will fire properly. Then I modified the custom function to just
immediately return a value =1 and exit. The event still fails that way though.

My immediate kludge concept is to replace the function with a macro that I'll
somehow force to execute (200 times) before I actually use the result from that
cell. I'm certainly open to a better approach though if you have one to
suggest. Or I may spend tomorrow dinking around with the function to see if I
can figure what's strange about it although all it does now is return =1.

Thanks...

Bill
-------------------------------
Dave Peterson wrote:


How are you opening the workbook?

File|open or double clicking on its filename in Windows Explorer?

Or are you opening the workbook using code in another workbook or a shortcut key
that includes the shift button?

If you add:

Private Sub Workbook_Open()
msgbox "hi from workbook_open"
Call AddButtons
End Sub

do you see the msgbox (Maybe it's a problem with addbuttons???).

========
If worse came to worse, maybe just moving that workbook_open procedure to a
general module and renaming it to Auto_open() would be a workaround???

Bill Martin wrote:



Is there any trick to getting the Workbook_Open() event to work? I currently
have a workbook with the following calls in the ThisWorkbook code module:

Option Explicit
Private Sub Workbook_Activate()
Call AddButtons
End Sub

Private Sub Workbook_Deactivate()
Call DeleteButtons
End Sub

Private Sub Workbook_Open()
Call AddButtons
End Sub

It compiles properly and works flawlessly as I switch back and forth between
workbooks, but it never seems to work when I first open a workbook. I can open
two different workbooks that have this code and get no buttons created. Then I
switch between them and the buttons come and go as expected. I'm using Excel97
if that makes any difference.

I've also tried immediately disabling events upon entering Workbook_Open, then
executing the Call and finally enabling events again before exiting but it makes
no difference. It just seems like the event never triggers. Or perhaps I'm
misunderstanding what it should do?

Thanks.

Bill

Workbook_Open() Event
 

Thinking about it a bit, I also just tried disabling auto calculation. When the
file opens in that mode the Workbook_Open event triggers properly.

Perhaps it's reasonable to assume that if there is an error found in a cell
somewhere during calc, that somehow disables events temporarily? I notice that
as Excel is loading (with autocalc turned on) some of my UDF cells have Value
error messages in them that disappear as the calc propagates through the sheets
- which may take 15 seconds.

Bill
--------------------------
Bill Martin wrote:
Oops. I just realized UDF = User Defined Function. I'll look into your other
recommendations and see what happens.

Thanks...

Bill
-----------------------------
Bill Martin wrote:

Pardon my being dense, but what is a UDF? I looked in the Excel help system and
both of Walkenbach's books but I don't find that term - not to say that it isn't
in there somewhere, but it's not indexed anyhow.

Bill
----------------------------
Dave Peterson wrote:


Is there any chance that you have an error in the UDF? If there is, maybe it's
screwing other things up???

Did you try putting the code into Auto_Open() (in a general module)?

And I've seen people recommend code like this when there seems to be a timing
issue:

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

Then in a general module:

sub Continue_Open()
'your real code here
call addbuttons
end sub

But all these are just guesses.

Good luck.


Bill Martin wrote:



It does not seem to matter how I open it Dave. To verify I just tried all ways
and always get the same failure. Adding the debug message does not help. That
code never executes.

After mulling it over and putzing with various things, I started stripping stuff
out of the workbook -- about 200 sheets deleted and a couple hours later I
finally found out what's going on, though I still don't understand it.

I have a custom function which I use to look at a column of randomly organized
numbers and return the average of the largest "n" of the numbers - "n" being
specified on another sheet. This function is used once on each of a couple
hundred sheets.

Anyhow, if I delete that function from all sheets then when the workbook opens
the event will fire properly. Then I modified the custom function to just
immediately return a value =1 and exit. The event still fails that way though.

My immediate kludge concept is to replace the function with a macro that I'll
somehow force to execute (200 times) before I actually use the result from that
cell. I'm certainly open to a better approach though if you have one to
suggest. Or I may spend tomorrow dinking around with the function to see if I
can figure what's strange about it although all it does now is return =1.

Thanks...

Bill
-------------------------------
Dave Peterson wrote:



How are you opening the workbook?

File|open or double clicking on its filename in Windows Explorer?

Or are you opening the workbook using code in another workbook or a shortcut key
that includes the shift button?

If you add:

Private Sub Workbook_Open()
msgbox "hi from workbook_open"
Call AddButtons
End Sub

do you see the msgbox (Maybe it's a problem with addbuttons???).

========
If worse came to worse, maybe just moving that workbook_open procedure to a
general module and renaming it to Auto_open() would be a workaround???

Bill Martin wrote:




Is there any trick to getting the Workbook_Open() event to work? I currently
have a workbook with the following calls in the ThisWorkbook code module:

Option Explicit
Private Sub Workbook_Activate()
Call AddButtons
End Sub

Private Sub Workbook_Deactivate()
Call DeleteButtons
End Sub

Private Sub Workbook_Open()
Call AddButtons
End Sub

It compiles properly and works flawlessly as I switch back and forth between
workbooks, but it never seems to work when I first open a workbook. I can open
two different workbooks that have this code and get no buttons created. Then I
switch between them and the buttons come and go as expected. I'm using Excel97
if that makes any difference.

I've also tried immediately disabling events upon entering Workbook_Open, then
executing the Call and finally enabling events again before exiting but it makes
no difference. It just seems like the event never triggers. Or perhaps I'm
misunderstanding what it should do?

Thanks.

Bill

Workbook_Open() Event
 

I think it would be dependent on the error.

And I think this is more of a bug (in code) than a feature of excel.

You may want to just double check that your UDF handles every possibility of an
error in a "nice" manner.

Unhandled errors can lead to unexpected results (absolutely no help, but a nice
phrase!).

Bill Martin wrote:

Thinking about it a bit, I also just tried disabling auto calculation. When the
file opens in that mode the Workbook_Open event triggers properly.

Perhaps it's reasonable to assume that if there is an error found in a cell
somewhere during calc, that somehow disables events temporarily? I notice that
as Excel is loading (with autocalc turned on) some of my UDF cells have Value
error messages in them that disappear as the calc propagates through the sheets
- which may take 15 seconds.

Bill
--------------------------
Bill Martin wrote:
Oops. I just realized UDF = User Defined Function. I'll look into your other
recommendations and see what happens.

Thanks...

Bill
-----------------------------
Bill Martin wrote:

Pardon my being dense, but what is a UDF? I looked in the Excel help system and
both of Walkenbach's books but I don't find that term - not to say that it isn't
in there somewhere, but it's not indexed anyhow.

Bill
----------------------------
Dave Peterson wrote:


Is there any chance that you have an error in the UDF? If there is, maybe it's
screwing other things up???

Did you try putting the code into Auto_Open() (in a general module)?

And I've seen people recommend code like this when there seems to be a timing
issue:

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

Then in a general module:

sub Continue_Open()
'your real code here
call addbuttons
end sub

But all these are just guesses.

Good luck.


Bill Martin wrote:



It does not seem to matter how I open it Dave. To verify I just tried all ways
and always get the same failure. Adding the debug message does not help. That
code never executes.

After mulling it over and putzing with various things, I started stripping stuff
out of the workbook -- about 200 sheets deleted and a couple hours later I
finally found out what's going on, though I still don't understand it.

I have a custom function which I use to look at a column of randomly organized
numbers and return the average of the largest "n" of the numbers - "n" being
specified on another sheet. This function is used once on each of a couple
hundred sheets.

Anyhow, if I delete that function from all sheets then when the workbook opens
the event will fire properly. Then I modified the custom function to just
immediately return a value =1 and exit. The event still fails that way though.

My immediate kludge concept is to replace the function with a macro that I'll
somehow force to execute (200 times) before I actually use the result from that
cell. I'm certainly open to a better approach though if you have one to
suggest. Or I may spend tomorrow dinking around with the function to see if I
can figure what's strange about it although all it does now is return =1.

Thanks...

Bill
-------------------------------
Dave Peterson wrote:



How are you opening the workbook?

File|open or double clicking on its filename in Windows Explorer?

Or are you opening the workbook using code in another workbook or a shortcut key
that includes the shift button?

If you add:

Private Sub Workbook_Open()
msgbox "hi from workbook_open"
Call AddButtons
End Sub

do you see the msgbox (Maybe it's a problem with addbuttons???).

========
If worse came to worse, maybe just moving that workbook_open procedure to a
general module and renaming it to Auto_open() would be a workaround???

Bill Martin wrote:




Is there any trick to getting the Workbook_Open() event to work? I currently
have a workbook with the following calls in the ThisWorkbook code module:

Option Explicit
Private Sub Workbook_Activate()
Call AddButtons
End Sub

Private Sub Workbook_Deactivate()
Call DeleteButtons
End Sub

Private Sub Workbook_Open()
Call AddButtons
End Sub

It compiles properly and works flawlessly as I switch back and forth between
workbooks, but it never seems to work when I first open a workbook. I can open
two different workbooks that have this code and get no buttons created. Then I
switch between them and the buttons come and go as expected. I'm using Excel97
if that makes any difference.

I've also tried immediately disabling events upon entering Workbook_Open, then
executing the Call and finally enabling events again before exiting but it makes
no difference. It just seems like the event never triggers. Or perhaps I'm
misunderstanding what it should do?

Thanks.

Bill



--

Dave Peterson

Workbook_Open() Event
 

My UDF at present consists of some simple DIM statements followed by:

UDFname = 1
exit function

Even with it this simple the function doesn't allow the event to trigger. I
suspect there's something more insidious. Since I can fix it now by running a
macro that replaces all the function cells by a calculated fixed value (or by
running another macro that puts the function back in the cells on the rare
occasion I actually need it) I'm going to let this slide until I get my hands on
a newer version of Excel. If the problem still exists there, then I may pursue
it further as an academic exercise.

Thanks.

Bill
--------------------------------
Dave Peterson wrote:
I think it would be dependent on the error.

And I think this is more of a bug (in code) than a feature of excel.

You may want to just double check that your UDF handles every possibility of an
error in a "nice" manner.

Unhandled errors can lead to unexpected results (absolutely no help, but a nice
phrase!).

Bill Martin wrote:

Thinking about it a bit, I also just tried disabling auto calculation. When the
file opens in that mode the Workbook_Open event triggers properly.

Perhaps it's reasonable to assume that if there is an error found in a cell
somewhere during calc, that somehow disables events temporarily? I notice that
as Excel is loading (with autocalc turned on) some of my UDF cells have Value
error messages in them that disappear as the calc propagates through the sheets
- which may take 15 seconds.

Bill
--------------------------
Bill Martin wrote:

Oops. I just realized UDF = User Defined Function. I'll look into your other
recommendations and see what happens.

Thanks...

Bill
-----------------------------
Bill Martin wrote:


Pardon my being dense, but what is a UDF? I looked in the Excel help system and
both of Walkenbach's books but I don't find that term - not to say that it isn't
in there somewhere, but it's not indexed anyhow.

Bill
----------------------------
Dave Peterson wrote:



Is there any chance that you have an error in the UDF? If there is, maybe it's
screwing other things up???

Did you try putting the code into Auto_Open() (in a general module)?

And I've seen people recommend code like this when there seems to be a timing
issue:

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

Then in a general module:

sub Continue_Open()
'your real code here
call addbuttons
end sub

But all these are just guesses.

Good luck.


Bill Martin wrote:




It does not seem to matter how I open it Dave. To verify I just tried all ways
and always get the same failure. Adding the debug message does not help. That
code never executes.

After mulling it over and putzing with various things, I started stripping stuff
out of the workbook -- about 200 sheets deleted and a couple hours later I
finally found out what's going on, though I still don't understand it.

I have a custom function which I use to look at a column of randomly organized
numbers and return the average of the largest "n" of the numbers - "n" being
specified on another sheet. This function is used once on each of a couple
hundred sheets.

Anyhow, if I delete that function from all sheets then when the workbook opens
the event will fire properly. Then I modified the custom function to just
immediately return a value =1 and exit. The event still fails that way though.

My immediate kludge concept is to replace the function with a macro that I'll
somehow force to execute (200 times) before I actually use the result from that
cell. I'm certainly open to a better approach though if you have one to
suggest. Or I may spend tomorrow dinking around with the function to see if I
can figure what's strange about it although all it does now is return =1.

Thanks...

Bill
-------------------------------
Dave Peterson wrote:




How are you opening the workbook?

File|open or double clicking on its filename in Windows Explorer?

Or are you opening the workbook using code in another workbook or a shortcut key
that includes the shift button?

If you add:

Private Sub Workbook_Open()
msgbox "hi from workbook_open"
Call AddButtons
End Sub

do you see the msgbox (Maybe it's a problem with addbuttons???).

========
If worse came to worse, maybe just moving that workbook_open procedure to a
general module and renaming it to Auto_open() would be a workaround???

Bill Martin wrote:





Is there any trick to getting the Workbook_Open() event to work? I currently
have a workbook with the following calls in the ThisWorkbook code module:

Option Explicit
Private Sub Workbook_Activate()
Call AddButtons
End Sub

Private Sub Workbook_Deactivate()
Call DeleteButtons
End Sub

Private Sub Workbook_Open()
Call AddButtons
End Sub

It compiles properly and works flawlessly as I switch back and forth between
workbooks, but it never seems to work when I first open a workbook. I can open
two different workbooks that have this code and get no buttons created. Then I
switch between them and the buttons come and go as expected. I'm using Excel97
if that makes any difference.

I've also tried immediately disabling events upon entering Workbook_Open, then
executing the Call and finally enabling events again before exiting but it makes
no difference. It just seems like the event never triggers. Or perhaps I'm
misunderstanding what it should do?

Thanks.

Bill

Workbook_Open() Event
 

Good luck,

Bill Martin wrote:

My UDF at present consists of some simple DIM statements followed by:

UDFname = 1
exit function

Even with it this simple the function doesn't allow the event to trigger. I
suspect there's something more insidious. Since I can fix it now by running a
macro that replaces all the function cells by a calculated fixed value (or by
running another macro that puts the function back in the cells on the rare
occasion I actually need it) I'm going to let this slide until I get my hands on
a newer version of Excel. If the problem still exists there, then I may pursue
it further as an academic exercise.

Thanks.

Bill
--------------------------------
Dave Peterson wrote:
I think it would be dependent on the error.

And I think this is more of a bug (in code) than a feature of excel.

You may want to just double check that your UDF handles every possibility of an
error in a "nice" manner.

Unhandled errors can lead to unexpected results (absolutely no help, but a nice
phrase!).

Bill Martin wrote:

Thinking about it a bit, I also just tried disabling auto calculation. When the
file opens in that mode the Workbook_Open event triggers properly.

Perhaps it's reasonable to assume that if there is an error found in a cell
somewhere during calc, that somehow disables events temporarily? I notice that
as Excel is loading (with autocalc turned on) some of my UDF cells have Value
error messages in them that disappear as the calc propagates through the sheets
- which may take 15 seconds.

Bill
--------------------------
Bill Martin wrote:

Oops. I just realized UDF = User Defined Function. I'll look into your other
recommendations and see what happens.

Thanks...

Bill
-----------------------------
Bill Martin wrote:


Pardon my being dense, but what is a UDF? I looked in the Excel help system and
both of Walkenbach's books but I don't find that term - not to say that it isn't
in there somewhere, but it's not indexed anyhow.

Bill
----------------------------
Dave Peterson wrote:



Is there any chance that you have an error in the UDF? If there is, maybe it's
screwing other things up???

Did you try putting the code into Auto_Open() (in a general module)?

And I've seen people recommend code like this when there seems to be a timing
issue:

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

Then in a general module:

sub Continue_Open()
'your real code here
call addbuttons
end sub

But all these are just guesses.

Good luck.


Bill Martin wrote:




It does not seem to matter how I open it Dave. To verify I just tried all ways
and always get the same failure. Adding the debug message does not help. That
code never executes.

After mulling it over and putzing with various things, I started stripping stuff
out of the workbook -- about 200 sheets deleted and a couple hours later I
finally found out what's going on, though I still don't understand it.

I have a custom function which I use to look at a column of randomly organized
numbers and return the average of the largest "n" of the numbers - "n" being
specified on another sheet. This function is used once on each of a couple
hundred sheets.

Anyhow, if I delete that function from all sheets then when the workbook opens
the event will fire properly. Then I modified the custom function to just
immediately return a value =1 and exit. The event still fails that way though.

My immediate kludge concept is to replace the function with a macro that I'll
somehow force to execute (200 times) before I actually use the result from that
cell. I'm certainly open to a better approach though if you have one to
suggest. Or I may spend tomorrow dinking around with the function to see if I
can figure what's strange about it although all it does now is return =1.

Thanks...

Bill
-------------------------------
Dave Peterson wrote:




How are you opening the workbook?

File|open or double clicking on its filename in Windows Explorer?

Or are you opening the workbook using code in another workbook or a shortcut key
that includes the shift button?

If you add:

Private Sub Workbook_Open()
msgbox "hi from workbook_open"
Call AddButtons
End Sub

do you see the msgbox (Maybe it's a problem with addbuttons???).

========
If worse came to worse, maybe just moving that workbook_open procedure to a
general module and renaming it to Auto_open() would be a workaround???

Bill Martin wrote:





Is there any trick to getting the Workbook_Open() event to work? I currently
have a workbook with the following calls in the ThisWorkbook code module:

Option Explicit
Private Sub Workbook_Activate()
Call AddButtons
End Sub

Private Sub Workbook_Deactivate()
Call DeleteButtons
End Sub

Private Sub Workbook_Open()
Call AddButtons
End Sub

It compiles properly and works flawlessly as I switch back and forth between
workbooks, but it never seems to work when I first open a workbook. I can open
two different workbooks that have this code and get no buttons created. Then I
switch between them and the buttons come and go as expected. I'm using Excel97
if that makes any difference.

I've also tried immediately disabling events upon entering Workbook_Open, then
executing the Call and finally enabling events again before exiting but it makes
no difference. It just seems like the event never triggers. Or perhaps I'm
misunderstanding what it should do?

Thanks.

Bill




--

Dave Peterson