software

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.

BillHow 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 quot;hi from workbook_openquot;
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:
gt;
gt; Is there any trick to getting the Workbook_Open() event to work? I currently
gt; have a workbook with the following calls in the ThisWorkbook code module:
gt;
gt; Option Explicit
gt; Private Sub Workbook_Activate()
gt; Call AddButtons
gt; End Sub
gt;
gt; Private Sub Workbook_Deactivate()
gt; Call DeleteButtons
gt; End Sub
gt;
gt; Private Sub Workbook_Open()
gt; Call AddButtons
gt; End Sub
gt;
gt; It compiles properly and works flawlessly as I switch back and forth between
gt; workbooks, but it never seems to work when I first open a workbook. I can open
gt; two different workbooks that have this code and get no buttons created. Then I
gt; switch between them and the buttons come and go as expected. I'm using Excel97
gt; if that makes any difference.
gt;
gt; I've also tried immediately disabling events upon entering Workbook_Open, then
gt; executing the Call and finally enabling events again before exiting but it makes
gt; no difference. It just seems like the event never triggers. Or perhaps I'm
gt; misunderstanding what it should do?
gt;
gt; Thanks.
gt;
gt; Bill

--

Dave Peterson

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 quot;nquot; of the numbers - quot;nquot; 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:
gt; How are you opening the workbook?
gt;
gt; File|open or double clicking on its filename in Windows Explorer?
gt;
gt; Or are you opening the workbook using code in another workbook or a shortcut key
gt; that includes the shift button?
gt;
gt; If you add:
gt;
gt; Private Sub Workbook_Open()
gt; msgbox quot;hi from workbook_openquot;
gt; Call AddButtons
gt; End Sub
gt;
gt; do you see the msgbox (Maybe it's a problem with addbuttons???).
gt;
gt; ========
gt; If worse came to worse, maybe just moving that workbook_open procedure to a
gt; general module and renaming it to Auto_open() would be a workaround???
gt;
gt; Bill Martin wrote:
gt;
gt;gt;Is there any trick to getting the Workbook_Open() event to work? I currently
gt;gt;have a workbook with the following calls in the ThisWorkbook code module:
gt;gt;
gt;gt; Option Explicit
gt;gt; Private Sub Workbook_Activate()
gt;gt; Call AddButtons
gt;gt; End Sub
gt;gt;
gt;gt; Private Sub Workbook_Deactivate()
gt;gt; Call DeleteButtons
gt;gt; End Sub
gt;gt;
gt;gt; Private Sub Workbook_Open()
gt;gt; Call AddButtons
gt;gt; End Sub
gt;gt;
gt;gt;It compiles properly and works flawlessly as I switch back and forth between
gt;gt;workbooks, but it never seems to work when I first open a workbook. I can open
gt;gt;two different workbooks that have this code and get no buttons created. Then I
gt;gt;switch between them and the buttons come and go as expected. I'm using Excel97
gt;gt;if that makes any difference.
gt;gt;
gt;gt;I've also tried immediately disabling events upon entering Workbook_Open, then
gt;gt;executing the Call and finally enabling events again before exiting but it makes
gt;gt;no difference. It just seems like the event never triggers. Or perhaps I'm
gt;gt;misunderstanding what it should do?
gt;gt;
gt;gt;Thanks.
gt;gt;
gt;gt;Bill
gt;
gt;

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, quot;Continue_Openquot;
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:
gt;
gt; It does not seem to matter how I open it Dave. To verify I just tried all ways
gt; and always get the same failure. Adding the debug message does not help. That
gt; code never executes.
gt;
gt; After mulling it over and putzing with various things, I started stripping stuff
gt; out of the workbook -- about 200 sheets deleted and a couple hours later I
gt; finally found out what's going on, though I still don't understand it.
gt;
gt; I have a custom function which I use to look at a column of randomly organized
gt; numbers and return the average of the largest quot;nquot; of the numbers - quot;nquot; being
gt; specified on another sheet. This function is used once on each of a couple
gt; hundred sheets.
gt;
gt; Anyhow, if I delete that function from all sheets then when the workbook opens
gt; the event will fire properly. Then I modified the custom function to just
gt; immediately return a value =1 and exit. The event still fails that way though.
gt;
gt; My immediate kludge concept is to replace the function with a macro that I'll
gt; somehow force to execute (200 times) before I actually use the result from that
gt; cell. I'm certainly open to a better approach though if you have one to
gt; suggest. Or I may spend tomorrow dinking around with the function to see if I
gt; can figure what's strange about it although all it does now is return =1.
gt;
gt; Thanks...
gt;
gt; Bill
gt; -------------------------------
gt; Dave Peterson wrote:
gt; gt; How are you opening the workbook?
gt; gt;
gt; gt; File|open or double clicking on its filename in Windows Explorer?
gt; gt;
gt; gt; Or are you opening the workbook using code in another workbook or a shortcut key
gt; gt; that includes the shift button?
gt; gt;
gt; gt; If you add:
gt; gt;
gt; gt; Private Sub Workbook_Open()
gt; gt; msgbox quot;hi from workbook_openquot;
gt; gt; Call AddButtons
gt; gt; End Sub
gt; gt;
gt; gt; do you see the msgbox (Maybe it's a problem with addbuttons???).
gt; gt;
gt; gt; ========
gt; gt; If worse came to worse, maybe just moving that workbook_open procedure to a
gt; gt; general module and renaming it to Auto_open() would be a workaround???
gt; gt;
gt; gt; Bill Martin wrote:
gt; gt;
gt; gt;gt;Is there any trick to getting the Workbook_Open() event to work? I currently
gt; gt;gt;have a workbook with the following calls in the ThisWorkbook code module:
gt; gt;gt;
gt; gt;gt; Option Explicit
gt; gt;gt; Private Sub Workbook_Activate()
gt; gt;gt; Call AddButtons
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt; Private Sub Workbook_Deactivate()
gt; gt;gt; Call DeleteButtons
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt; Private Sub Workbook_Open()
gt; gt;gt; Call AddButtons
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt;It compiles properly and works flawlessly as I switch back and forth between
gt; gt;gt;workbooks, but it never seems to work when I first open a workbook. I can open
gt; gt;gt;two different workbooks that have this code and get no buttons created. Then I
gt; gt;gt;switch between them and the buttons come and go as expected. I'm using Excel97
gt; gt;gt;if that makes any difference.
gt; gt;gt;
gt; gt;gt;I've also tried immediately disabling events upon entering Workbook_Open, then
gt; gt;gt;executing the Call and finally enabling events again before exiting but it makes
gt; gt;gt;no difference. It just seems like the event never triggers. Or perhaps I'm
gt; gt;gt;misunderstanding what it should do?
gt; gt;gt;
gt; gt;gt;Thanks.
gt; gt;gt;
gt; gt;gt;Bill
gt; gt;
gt; gt;

--

Dave Peterson

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:
gt; Is there any chance that you have an error in the UDF? If there is, maybe it's
gt; screwing other things up???
gt;
gt; Did you try putting the code into Auto_Open() (in a general module)?
gt;
gt; And I've seen people recommend code like this when there seems to be a timing
gt; issue:
gt;
gt; Private Sub Workbook_Open()
gt; Application.OnTime Now, quot;Continue_Openquot;
gt; End Sub
gt;
gt; Then in a general module:
gt;
gt; sub Continue_Open()
gt; 'your real code here
gt; call addbuttons
gt; end sub
gt;
gt; But all these are just guesses.
gt;
gt; Good luck.
gt;
gt;
gt; Bill Martin wrote:
gt;
gt;gt;It does not seem to matter how I open it Dave. To verify I just tried all ways
gt;gt;and always get the same failure. Adding the debug message does not help. That
gt;gt;code never executes.
gt;gt;
gt;gt;After mulling it over and putzing with various things, I started stripping stuff
gt;gt;out of the workbook -- about 200 sheets deleted and a couple hours later I
gt;gt;finally found out what's going on, though I still don't understand it.
gt;gt;
gt;gt;I have a custom function which I use to look at a column of randomly organized
gt;gt;numbers and return the average of the largest quot;nquot; of the numbers - quot;nquot; being
gt;gt;specified on another sheet. This function is used once on each of a couple
gt;gt;hundred sheets.
gt;gt;
gt;gt;Anyhow, if I delete that function from all sheets then when the workbook opens
gt;gt;the event will fire properly. Then I modified the custom function to just
gt;gt;immediately return a value =1 and exit. The event still fails that way though.
gt;gt;
gt;gt;My immediate kludge concept is to replace the function with a macro that I'll
gt;gt;somehow force to execute (200 times) before I actually use the result from that
gt;gt;cell. I'm certainly open to a better approach though if you have one to
gt;gt;suggest. Or I may spend tomorrow dinking around with the function to see if I
gt;gt;can figure what's strange about it although all it does now is return =1.
gt;gt;
gt;gt;Thanks...
gt;gt;
gt;gt;Bill
gt;gt;-------------------------------
gt;gt;Dave Peterson wrote:
gt;gt;
gt;gt;gt;How are you opening the workbook?
gt;gt;gt;
gt;gt;gt;File|open or double clicking on its filename in Windows Explorer?
gt;gt;gt;
gt;gt;gt;Or are you opening the workbook using code in another workbook or a shortcut key
gt;gt;gt;that includes the shift button?
gt;gt;gt;
gt;gt;gt;If you add:
gt;gt;gt;
gt;gt;gt; Private Sub Workbook_Open()
gt;gt;gt; msgbox quot;hi from workbook_openquot;
gt;gt;gt; Call AddButtons
gt;gt;gt; End Sub
gt;gt;gt;
gt;gt;gt;do you see the msgbox (Maybe it's a problem with addbuttons???).
gt;gt;gt;
gt;gt;gt;========
gt;gt;gt;If worse came to worse, maybe just moving that workbook_open procedure to a
gt;gt;gt;general module and renaming it to Auto_open() would be a workaround???
gt;gt;gt;
gt;gt;gt;Bill Martin wrote:
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;gt;Is there any trick to getting the Workbook_Open() event to work? I currently
gt;gt;gt;gt;have a workbook with the following calls in the ThisWorkbook code module:
gt;gt;gt;gt;
gt;gt;gt;gt; Option Explicit
gt;gt;gt;gt; Private Sub Workbook_Activate()
gt;gt;gt;gt; Call AddButtons
gt;gt;gt;gt; End Sub
gt;gt;gt;gt;
gt;gt;gt;gt; Private Sub Workbook_Deactivate()
gt;gt;gt;gt; Call DeleteButtons
gt;gt;gt;gt; End Sub
gt;gt;gt;gt;
gt;gt;gt;gt; Private Sub Workbook_Open()
gt;gt;gt;gt; Call AddButtons
gt;gt;gt;gt; End Sub
gt;gt;gt;gt;
gt;gt;gt;gt;It compiles properly and works flawlessly as I switch back and forth between
gt;gt;gt;gt;workbooks, but it never seems to work when I first open a workbook. I can open
gt;gt;gt;gt;two different workbooks that have this code and get no buttons created. Then I
gt;gt;gt;gt;switch between them and the buttons come and go as expected. I'm using Excel97
gt;gt;gt;gt;if that makes any difference.
gt;gt;gt;gt;
gt;gt;gt;gt;I've also tried immediately disabling events upon entering Workbook_Open, then
gt;gt;gt;gt;executing the Call and finally enabling events again before exiting but it makes
gt;gt;gt;gt;no difference. It just seems like the event never triggers. Or perhaps I'm
gt;gt;gt;gt;misunderstanding what it should do?
gt;gt;gt;gt;
gt;gt;gt;gt;Thanks.
gt;gt;gt;gt;
gt;gt;gt;gt;Bill
gt;gt;gt;
gt;gt;gt;
gt;

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

Thanks...

Bill
-----------------------------
Bill Martin wrote:
gt; Pardon my being dense, but what is a UDF? I looked in the Excel help system and
gt; both of Walkenbach's books but I don't find that term - not to say that it isn't
gt; in there somewhere, but it's not indexed anyhow.
gt;
gt; Bill
gt; ----------------------------
gt; Dave Peterson wrote:
gt;
gt;gt;Is there any chance that you have an error in the UDF? If there is, maybe it's
gt;gt;screwing other things up???
gt;gt;
gt;gt;Did you try putting the code into Auto_Open() (in a general module)?
gt;gt;
gt;gt;And I've seen people recommend code like this when there seems to be a timing
gt;gt;issue:
gt;gt;
gt;gt;Private Sub Workbook_Open()
gt;gt; Application.OnTime Now, quot;Continue_Openquot;
gt;gt;End Sub
gt;gt;
gt;gt;Then in a general module:
gt;gt;
gt;gt;sub Continue_Open()
gt;gt; 'your real code here
gt;gt; call addbuttons
gt;gt;end sub
gt;gt;
gt;gt;But all these are just guesses.
gt;gt;
gt;gt;Good luck.
gt;gt;
gt;gt;
gt;gt;Bill Martin wrote:
gt;gt;
gt;gt;
gt;gt;gt;It does not seem to matter how I open it Dave. To verify I just tried all ways
gt;gt;gt;and always get the same failure. Adding the debug message does not help. That
gt;gt;gt;code never executes.
gt;gt;gt;
gt;gt;gt;After mulling it over and putzing with various things, I started stripping stuff
gt;gt;gt;out of the workbook -- about 200 sheets deleted and a couple hours later I
gt;gt;gt;finally found out what's going on, though I still don't understand it.
gt;gt;gt;
gt;gt;gt;I have a custom function which I use to look at a column of randomly organized
gt;gt;gt;numbers and return the average of the largest quot;nquot; of the numbers - quot;nquot; being
gt;gt;gt;specified on another sheet. This function is used once on each of a couple
gt;gt;gt;hundred sheets.
gt;gt;gt;
gt;gt;gt;Anyhow, if I delete that function from all sheets then when the workbook opens
gt;gt;gt;the event will fire properly. Then I modified the custom function to just
gt;gt;gt;immediately return a value =1 and exit. The event still fails that way though.
gt;gt;gt;
gt;gt;gt;My immediate kludge concept is to replace the function with a macro that I'll
gt;gt;gt;somehow force to execute (200 times) before I actually use the result from that
gt;gt;gt;cell. I'm certainly open to a better approach though if you have one to
gt;gt;gt;suggest. Or I may spend tomorrow dinking around with the function to see if I
gt;gt;gt;can figure what's strange about it although all it does now is return =1.
gt;gt;gt;
gt;gt;gt;Thanks...
gt;gt;gt;
gt;gt;gt;Bill
gt;gt;gt;-------------------------------
gt;gt;gt;Dave Peterson wrote:
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;gt;How are you opening the workbook?
gt;gt;gt;gt;
gt;gt;gt;gt;File|open or double clicking on its filename in Windows Explorer?
gt;gt;gt;gt;
gt;gt;gt;gt;Or are you opening the workbook using code in another workbook or a shortcut key
gt;gt;gt;gt;that includes the shift button?
gt;gt;gt;gt;
gt;gt;gt;gt;If you add:
gt;gt;gt;gt;
gt;gt;gt;gt; Private Sub Workbook_Open()
gt;gt;gt;gt; msgbox quot;hi from workbook_openquot;
gt;gt;gt;gt; Call AddButtons
gt;gt;gt;gt; End Sub
gt;gt;gt;gt;
gt;gt;gt;gt;do you see the msgbox (Maybe it's a problem with addbuttons???).
gt;gt;gt;gt;
gt;gt;gt;gt;========
gt;gt;gt;gt;If worse came to worse, maybe just moving that workbook_open procedure to a
gt;gt;gt;gt;general module and renaming it to Auto_open() would be a workaround???
gt;gt;gt;gt;
gt;gt;gt;gt;Bill Martin wrote:
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;gt;Is there any trick to getting the Workbook_Open() event to work? I currently
gt;gt;gt;gt;gt;have a workbook with the following calls in the ThisWorkbook code module:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Option Explicit
gt;gt;gt;gt;gt; Private Sub Workbook_Activate()
gt;gt;gt;gt;gt; Call AddButtons
gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Private Sub Workbook_Deactivate()
gt;gt;gt;gt;gt; Call DeleteButtons
gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Private Sub Workbook_Open()
gt;gt;gt;gt;gt; Call AddButtons
gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;It compiles properly and works flawlessly as I switch back and forth between
gt;gt;gt;gt;gt;workbooks, but it never seems to work when I first open a workbook. I can open
gt;gt;gt;gt;gt;two different workbooks that have this code and get no buttons created. Then I
gt;gt;gt;gt;gt;switch between them and the buttons come and go as expected. I'm using Excel97
gt;gt;gt;gt;gt;if that makes any difference.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;I've also tried immediately disabling events upon entering Workbook_Open, then
gt;gt;gt;gt;gt;executing the Call and finally enabling events again before exiting but it makes
gt;gt;gt;gt;gt;no difference. It just seems like the event never triggers. Or perhaps I'm
gt;gt;gt;gt;gt;misunderstanding what it should do?
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Thanks.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Bill
gt;gt;gt;gt;
gt;gt;gt;gt;

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:
gt; Oops. I just realized UDF = User Defined Function. I'll look into your other
gt; recommendations and see what happens.
gt;
gt; Thanks...
gt;
gt; Bill
gt; -----------------------------
gt; Bill Martin wrote:
gt;
gt;gt;Pardon my being dense, but what is a UDF? I looked in the Excel help system and
gt;gt;both of Walkenbach's books but I don't find that term - not to say that it isn't
gt;gt;in there somewhere, but it's not indexed anyhow.
gt;gt;
gt;gt;Bill
gt;gt;----------------------------
gt;gt;Dave Peterson wrote:
gt;gt;
gt;gt;
gt;gt;gt;Is there any chance that you have an error in the UDF? If there is, maybe it's
gt;gt;gt;screwing other things up???
gt;gt;gt;
gt;gt;gt;Did you try putting the code into Auto_Open() (in a general module)?
gt;gt;gt;
gt;gt;gt;And I've seen people recommend code like this when there seems to be a timing
gt;gt;gt;issue:
gt;gt;gt;
gt;gt;gt;Private Sub Workbook_Open()
gt;gt;gt; Application.OnTime Now, quot;Continue_Openquot;
gt;gt;gt;End Sub
gt;gt;gt;
gt;gt;gt;Then in a general module:
gt;gt;gt;
gt;gt;gt;sub Continue_Open()
gt;gt;gt;'your real code here
gt;gt;gt; call addbuttons
gt;gt;gt;end sub
gt;gt;gt;
gt;gt;gt;But all these are just guesses.
gt;gt;gt;
gt;gt;gt;Good luck.
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;Bill Martin wrote:
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;gt;It does not seem to matter how I open it Dave. To verify I just tried all ways
gt;gt;gt;gt;and always get the same failure. Adding the debug message does not help. That
gt;gt;gt;gt;code never executes.
gt;gt;gt;gt;
gt;gt;gt;gt;After mulling it over and putzing with various things, I started stripping stuff
gt;gt;gt;gt;out of the workbook -- about 200 sheets deleted and a couple hours later I
gt;gt;gt;gt;finally found out what's going on, though I still don't understand it.
gt;gt;gt;gt;
gt;gt;gt;gt;I have a custom function which I use to look at a column of randomly organized
gt;gt;gt;gt;numbers and return the average of the largest quot;nquot; of the numbers - quot;nquot; being
gt;gt;gt;gt;specified on another sheet. This function is used once on each of a couple
gt;gt;gt;gt;hundred sheets.
gt;gt;gt;gt;
gt;gt;gt;gt;Anyhow, if I delete that function from all sheets then when the workbook opens
gt;gt;gt;gt;the event will fire properly. Then I modified the custom function to just
gt;gt;gt;gt;immediately return a value =1 and exit. The event still fails that way though.
gt;gt;gt;gt;
gt;gt;gt;gt;My immediate kludge concept is to replace the function with a macro that I'll
gt;gt;gt;gt;somehow force to execute (200 times) before I actually use the result from that
gt;gt;gt;gt;cell. I'm certainly open to a better approach though if you have one to
gt;gt;gt;gt;suggest. Or I may spend tomorrow dinking around with the function to see if I
gt;gt;gt;gt;can figure what's strange about it although all it does now is return =1.
gt;gt;gt;gt;
gt;gt;gt;gt;Thanks...
gt;gt;gt;gt;
gt;gt;gt;gt;Bill
gt;gt;gt;gt;-------------------------------
gt;gt;gt;gt;Dave Peterson wrote:
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;gt;How are you opening the workbook?
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;File|open or double clicking on its filename in Windows Explorer?
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Or are you opening the workbook using code in another workbook or a shortcut key
gt;gt;gt;gt;gt;that includes the shift button?
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;If you add:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Private Sub Workbook_Open()
gt;gt;gt;gt;gt; msgbox quot;hi from workbook_openquot;
gt;gt;gt;gt;gt; Call AddButtons
gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;do you see the msgbox (Maybe it's a problem with addbuttons???).
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;========
gt;gt;gt;gt;gt;If worse came to worse, maybe just moving that workbook_open procedure to a
gt;gt;gt;gt;gt;general module and renaming it to Auto_open() would be a workaround???
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Bill Martin wrote:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;Is there any trick to getting the Workbook_Open() event to work? I currently
gt;gt;gt;gt;gt;gt;have a workbook with the following calls in the ThisWorkbook code module:
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt; Option Explicit
gt;gt;gt;gt;gt;gt; Private Sub Workbook_Activate()
gt;gt;gt;gt;gt;gt; Call AddButtons
gt;gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt; Private Sub Workbook_Deactivate()
gt;gt;gt;gt;gt;gt; Call DeleteButtons
gt;gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt; Private Sub Workbook_Open()
gt;gt;gt;gt;gt;gt; Call AddButtons
gt;gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;It compiles properly and works flawlessly as I switch back and forth between
gt;gt;gt;gt;gt;gt;workbooks, but it never seems to work when I first open a workbook. I can open
gt;gt;gt;gt;gt;gt;two different workbooks that have this code and get no buttons created. Then I
gt;gt;gt;gt;gt;gt;switch between them and the buttons come and go as expected. I'm using Excel97
gt;gt;gt;gt;gt;gt;if that makes any difference.
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;I've also tried immediately disabling events upon entering Workbook_Open, then
gt;gt;gt;gt;gt;gt;executing the Call and finally enabling events again before exiting but it makes
gt;gt;gt;gt;gt;gt;no difference. It just seems like the event never triggers. Or perhaps I'm
gt;gt;gt;gt;gt;gt;misunderstanding what it should do?
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;Thanks.
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;Bill
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;

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 quot;nicequot; manner.

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

Bill Martin wrote:
gt;
gt; Thinking about it a bit, I also just tried disabling auto calculation. When the
gt; file opens in that mode the Workbook_Open event triggers properly.
gt;
gt; Perhaps it's reasonable to assume that if there is an error found in a cell
gt; somewhere during calc, that somehow disables events temporarily? I notice that
gt; as Excel is loading (with autocalc turned on) some of my UDF cells have Value
gt; error messages in them that disappear as the calc propagates through the sheets
gt; - which may take 15 seconds.
gt;
gt; Bill
gt; --------------------------
gt; Bill Martin wrote:
gt; gt; Oops. I just realized UDF = User Defined Function. I'll look into your other
gt; gt; recommendations and see what happens.
gt; gt;
gt; gt; Thanks...
gt; gt;
gt; gt; Bill
gt; gt; -----------------------------
gt; gt; Bill Martin wrote:
gt; gt;
gt; gt;gt;Pardon my being dense, but what is a UDF? I looked in the Excel help system and
gt; gt;gt;both of Walkenbach's books but I don't find that term - not to say that it isn't
gt; gt;gt;in there somewhere, but it's not indexed anyhow.
gt; gt;gt;
gt; gt;gt;Bill
gt; gt;gt;----------------------------
gt; gt;gt;Dave Peterson wrote:
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;gt;Is there any chance that you have an error in the UDF? If there is, maybe it's
gt; gt;gt;gt;screwing other things up???
gt; gt;gt;gt;
gt; gt;gt;gt;Did you try putting the code into Auto_Open() (in a general module)?
gt; gt;gt;gt;
gt; gt;gt;gt;And I've seen people recommend code like this when there seems to be a timing
gt; gt;gt;gt;issue:
gt; gt;gt;gt;
gt; gt;gt;gt;Private Sub Workbook_Open()
gt; gt;gt;gt; Application.OnTime Now, quot;Continue_Openquot;
gt; gt;gt;gt;End Sub
gt; gt;gt;gt;
gt; gt;gt;gt;Then in a general module:
gt; gt;gt;gt;
gt; gt;gt;gt;sub Continue_Open()
gt; gt;gt;gt;'your real code here
gt; gt;gt;gt; call addbuttons
gt; gt;gt;gt;end sub
gt; gt;gt;gt;
gt; gt;gt;gt;But all these are just guesses.
gt; gt;gt;gt;
gt; gt;gt;gt;Good luck.
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;Bill Martin wrote:
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;gt;It does not seem to matter how I open it Dave. To verify I just tried all ways
gt; gt;gt;gt;gt;and always get the same failure. Adding the debug message does not help. That
gt; gt;gt;gt;gt;code never executes.
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;After mulling it over and putzing with various things, I started stripping stuff
gt; gt;gt;gt;gt;out of the workbook -- about 200 sheets deleted and a couple hours later I
gt; gt;gt;gt;gt;finally found out what's going on, though I still don't understand it.
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;I have a custom function which I use to look at a column of randomly organized
gt; gt;gt;gt;gt;numbers and return the average of the largest quot;nquot; of the numbers - quot;nquot; being
gt; gt;gt;gt;gt;specified on another sheet. This function is used once on each of a couple
gt; gt;gt;gt;gt;hundred sheets.
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;Anyhow, if I delete that function from all sheets then when the workbook opens
gt; gt;gt;gt;gt;the event will fire properly. Then I modified the custom function to just
gt; gt;gt;gt;gt;immediately return a value =1 and exit. The event still fails that way though.
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;My immediate kludge concept is to replace the function with a macro that I'll
gt; gt;gt;gt;gt;somehow force to execute (200 times) before I actually use the result from that
gt; gt;gt;gt;gt;cell. I'm certainly open to a better approach though if you have one to
gt; gt;gt;gt;gt;suggest. Or I may spend tomorrow dinking around with the function to see if I
gt; gt;gt;gt;gt;can figure what's strange about it although all it does now is return =1.
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;Thanks...
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;Bill
gt; gt;gt;gt;gt;-------------------------------
gt; gt;gt;gt;gt;Dave Peterson wrote:
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;How are you opening the workbook?
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;File|open or double clicking on its filename in Windows Explorer?
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;Or are you opening the workbook using code in another workbook or a shortcut key
gt; gt;gt;gt;gt;gt;that includes the shift button?
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;If you add:
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt; Private Sub Workbook_Open()
gt; gt;gt;gt;gt;gt; msgbox quot;hi from workbook_openquot;
gt; gt;gt;gt;gt;gt; Call AddButtons
gt; gt;gt;gt;gt;gt; End Sub
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;do you see the msgbox (Maybe it's a problem with addbuttons???).
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;========
gt; gt;gt;gt;gt;gt;If worse came to worse, maybe just moving that workbook_open procedure to a
gt; gt;gt;gt;gt;gt;general module and renaming it to Auto_open() would be a workaround???
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;Bill Martin wrote:
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;Is there any trick to getting the Workbook_Open() event to work? I currently
gt; gt;gt;gt;gt;gt;gt;have a workbook with the following calls in the ThisWorkbook code module:
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt; Option Explicit
gt; gt;gt;gt;gt;gt;gt; Private Sub Workbook_Activate()
gt; gt;gt;gt;gt;gt;gt; Call AddButtons
gt; gt;gt;gt;gt;gt;gt; End Sub
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt; Private Sub Workbook_Deactivate()
gt; gt;gt;gt;gt;gt;gt; Call DeleteButtons
gt; gt;gt;gt;gt;gt;gt; End Sub
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt; Private Sub Workbook_Open()
gt; gt;gt;gt;gt;gt;gt; Call AddButtons
gt; gt;gt;gt;gt;gt;gt; End Sub
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;It compiles properly and works flawlessly as I switch back and forth between
gt; gt;gt;gt;gt;gt;gt;workbooks, but it never seems to work when I first open a workbook. I can open
gt; gt;gt;gt;gt;gt;gt;two different workbooks that have this code and get no buttons created. Then I
gt; gt;gt;gt;gt;gt;gt;switch between them and the buttons come and go as expected. I'm using Excel97
gt; gt;gt;gt;gt;gt;gt;if that makes any difference.
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;I've also tried immediately disabling events upon entering Workbook_Open, then
gt; gt;gt;gt;gt;gt;gt;executing the Call and finally enabling events again before exiting but it makes
gt; gt;gt;gt;gt;gt;gt;no difference. It just seems like the event never triggers. Or perhaps I'm
gt; gt;gt;gt;gt;gt;gt;misunderstanding what it should do?
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;Thanks.
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;Bill
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;

--

Dave Peterson

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:
gt; I think it would be dependent on the error.
gt;
gt; And I think this is more of a bug (in code) than a feature of excel.
gt;
gt; You may want to just double check that your UDF handles every possibility of an
gt; error in a quot;nicequot; manner.
gt;
gt; Unhandled errors can lead to unexpected results (absolutely no help, but a nice
gt; phrase!).
gt;
gt; Bill Martin wrote:
gt;
gt;gt;Thinking about it a bit, I also just tried disabling auto calculation. When the
gt;gt;file opens in that mode the Workbook_Open event triggers properly.
gt;gt;
gt;gt;Perhaps it's reasonable to assume that if there is an error found in a cell
gt;gt;somewhere during calc, that somehow disables events temporarily? I notice that
gt;gt;as Excel is loading (with autocalc turned on) some of my UDF cells have Value
gt;gt;error messages in them that disappear as the calc propagates through the sheets
gt;gt;- which may take 15 seconds.
gt;gt;
gt;gt;Bill
gt;gt;--------------------------
gt;gt;Bill Martin wrote:
gt;gt;
gt;gt;gt;Oops. I just realized UDF = User Defined Function. I'll look into your other
gt;gt;gt;recommendations and see what happens.
gt;gt;gt;
gt;gt;gt;Thanks...
gt;gt;gt;
gt;gt;gt;Bill
gt;gt;gt;-----------------------------
gt;gt;gt;Bill Martin wrote:
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;gt;Pardon my being dense, but what is a UDF? I looked in the Excel help system and
gt;gt;gt;gt;both of Walkenbach's books but I don't find that term - not to say that it isn't
gt;gt;gt;gt;in there somewhere, but it's not indexed anyhow.
gt;gt;gt;gt;
gt;gt;gt;gt;Bill
gt;gt;gt;gt;----------------------------
gt;gt;gt;gt;Dave Peterson wrote:
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;gt;Is there any chance that you have an error in the UDF? If there is, maybe it's
gt;gt;gt;gt;gt;screwing other things up???
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Did you try putting the code into Auto_Open() (in a general module)?
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;And I've seen people recommend code like this when there seems to be a timing
gt;gt;gt;gt;gt;issue:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Private Sub Workbook_Open()
gt;gt;gt;gt;gt;Application.OnTime Now, quot;Continue_Openquot;
gt;gt;gt;gt;gt;End Sub
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Then in a general module:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;sub Continue_Open()
gt;gt;gt;gt;gt;'your real code here
gt;gt;gt;gt;gt;call addbuttons
gt;gt;gt;gt;gt;end sub
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;But all these are just guesses.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Good luck.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Bill Martin wrote:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;It does not seem to matter how I open it Dave. To verify I just tried all ways
gt;gt;gt;gt;gt;gt;and always get the same failure. Adding the debug message does not help. That
gt;gt;gt;gt;gt;gt;code never executes.
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;After mulling it over and putzing with various things, I started stripping stuff
gt;gt;gt;gt;gt;gt;out of the workbook -- about 200 sheets deleted and a couple hours later I
gt;gt;gt;gt;gt;gt;finally found out what's going on, though I still don't understand it.
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;I have a custom function which I use to look at a column of randomly organized
gt;gt;gt;gt;gt;gt;numbers and return the average of the largest quot;nquot; of the numbers - quot;nquot; being
gt;gt;gt;gt;gt;gt;specified on another sheet. This function is used once on each of a couple
gt;gt;gt;gt;gt;gt;hundred sheets.
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;Anyhow, if I delete that function from all sheets then when the workbook opens
gt;gt;gt;gt;gt;gt;the event will fire properly. Then I modified the custom function to just
gt;gt;gt;gt;gt;gt;immediately return a value =1 and exit. The event still fails that way though.
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;My immediate kludge concept is to replace the function with a macro that I'll
gt;gt;gt;gt;gt;gt;somehow force to execute (200 times) before I actually use the result from that
gt;gt;gt;gt;gt;gt;cell. I'm certainly open to a better approach though if you have one to
gt;gt;gt;gt;gt;gt;suggest. Or I may spend tomorrow dinking around with the function to see if I
gt;gt;gt;gt;gt;gt;can figure what's strange about it although all it does now is return =1.
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;Thanks...
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;Bill
gt;gt;gt;gt;gt;gt;-------------------------------
gt;gt;gt;gt;gt;gt;Dave Peterson wrote:
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;How are you opening the workbook?
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;File|open or double clicking on its filename in Windows Explorer?
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;Or are you opening the workbook using code in another workbook or a shortcut key
gt;gt;gt;gt;gt;gt;gt;that includes the shift button?
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;If you add:
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt; Private Sub Workbook_Open()
gt;gt;gt;gt;gt;gt;gt; msgbox quot;hi from workbook_openquot;
gt;gt;gt;gt;gt;gt;gt; Call AddButtons
gt;gt;gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;do you see the msgbox (Maybe it's a problem with addbuttons???).
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;========
gt;gt;gt;gt;gt;gt;gt;If worse came to worse, maybe just moving that workbook_open procedure to a
gt;gt;gt;gt;gt;gt;gt;general module and renaming it to Auto_open() would be a workaround???
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;Bill Martin wrote:
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;gt;Is there any trick to getting the Workbook_Open() event to work? I currently
gt;gt;gt;gt;gt;gt;gt;gt;have a workbook with the following calls in the ThisWorkbook code module:
gt;gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;gt; Option Explicit
gt;gt;gt;gt;gt;gt;gt;gt; Private Sub Workbook_Activate()
gt;gt;gt;gt;gt;gt;gt;gt; Call AddButtons
gt;gt;gt;gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;gt; Private Sub Workbook_Deactivate()
gt;gt;gt;gt;gt;gt;gt;gt; Call DeleteButtons
gt;gt;gt;gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;gt; Private Sub Workbook_Open()
gt;gt;gt;gt;gt;gt;gt;gt; Call AddButtons
gt;gt;gt;gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;gt;It compiles properly and works flawlessly as I switch back and forth between
gt;gt;gt;gt;gt;gt;gt;gt;workbooks, but it never seems to work when I first open a workbook. I can open
gt;gt;gt;gt;gt;gt;gt;gt;two different workbooks that have this code and get no buttons created. Then I
gt;gt;gt;gt;gt;gt;gt;gt;switch between them and the buttons come and go as expected. I'm using Excel97
gt;gt;gt;gt;gt;gt;gt;gt;if that makes any difference.
gt;gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;gt;I've also tried immediately disabling events upon entering Workbook_Open, then
gt;gt;gt;gt;gt;gt;gt;gt;executing the Call and finally enabling events again before exiting but it makes
gt;gt;gt;gt;gt;gt;gt;gt;no difference. It just seems like the event never triggers. Or perhaps I'm
gt;gt;gt;gt;gt;gt;gt;gt;misunderstanding what it should do?
gt;gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;gt;Thanks.
gt;gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;gt;Bill
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;
gt;

Good luck,

Bill Martin wrote:
gt;
gt; My UDF at present consists of some simple DIM statements followed by:
gt;
gt; UDFname = 1
gt; exit function
gt;
gt; Even with it this simple the function doesn't allow the event to trigger. I
gt; suspect there's something more insidious. Since I can fix it now by running a
gt; macro that replaces all the function cells by a calculated fixed value (or by
gt; running another macro that puts the function back in the cells on the rare
gt; occasion I actually need it) I'm going to let this slide until I get my hands on
gt; a newer version of Excel. If the problem still exists there, then I may pursue
gt; it further as an academic exercise.
gt;
gt; Thanks.
gt;
gt; Bill
gt; --------------------------------
gt; Dave Peterson wrote:
gt; gt; I think it would be dependent on the error.
gt; gt;
gt; gt; And I think this is more of a bug (in code) than a feature of excel.
gt; gt;
gt; gt; You may want to just double check that your UDF handles every possibility of an
gt; gt; error in a quot;nicequot; manner.
gt; gt;
gt; gt; Unhandled errors can lead to unexpected results (absolutely no help, but a nice
gt; gt; phrase!).
gt; gt;
gt; gt; Bill Martin wrote:
gt; gt;
gt; gt;gt;Thinking about it a bit, I also just tried disabling auto calculation. When the
gt; gt;gt;file opens in that mode the Workbook_Open event triggers properly.
gt; gt;gt;
gt; gt;gt;Perhaps it's reasonable to assume that if there is an error found in a cell
gt; gt;gt;somewhere during calc, that somehow disables events temporarily? I notice that
gt; gt;gt;as Excel is loading (with autocalc turned on) some of my UDF cells have Value
gt; gt;gt;error messages in them that disappear as the calc propagates through the sheets
gt; gt;gt;- which may take 15 seconds.
gt; gt;gt;
gt; gt;gt;Bill
gt; gt;gt;--------------------------
gt; gt;gt;Bill Martin wrote:
gt; gt;gt;
gt; gt;gt;gt;Oops. I just realized UDF = User Defined Function. I'll look into your other
gt; gt;gt;gt;recommendations and see what happens.
gt; gt;gt;gt;
gt; gt;gt;gt;Thanks...
gt; gt;gt;gt;
gt; gt;gt;gt;Bill
gt; gt;gt;gt;-----------------------------
gt; gt;gt;gt;Bill Martin wrote:
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;gt;Pardon my being dense, but what is a UDF? I looked in the Excel help system and
gt; gt;gt;gt;gt;both of Walkenbach's books but I don't find that term - not to say that it isn't
gt; gt;gt;gt;gt;in there somewhere, but it's not indexed anyhow.
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;Bill
gt; gt;gt;gt;gt;----------------------------
gt; gt;gt;gt;gt;Dave Peterson wrote:
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;Is there any chance that you have an error in the UDF? If there is, maybe it's
gt; gt;gt;gt;gt;gt;screwing other things up???
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;Did you try putting the code into Auto_Open() (in a general module)?
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;And I've seen people recommend code like this when there seems to be a timing
gt; gt;gt;gt;gt;gt;issue:
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;Private Sub Workbook_Open()
gt; gt;gt;gt;gt;gt;Application.OnTime Now, quot;Continue_Openquot;
gt; gt;gt;gt;gt;gt;End Sub
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;Then in a general module:
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;sub Continue_Open()
gt; gt;gt;gt;gt;gt;'your real code here
gt; gt;gt;gt;gt;gt;call addbuttons
gt; gt;gt;gt;gt;gt;end sub
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;But all these are just guesses.
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;Good luck.
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;Bill Martin wrote:
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;It does not seem to matter how I open it Dave. To verify I just tried all ways
gt; gt;gt;gt;gt;gt;gt;and always get the same failure. Adding the debug message does not help. That
gt; gt;gt;gt;gt;gt;gt;code never executes.
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;After mulling it over and putzing with various things, I started stripping stuff
gt; gt;gt;gt;gt;gt;gt;out of the workbook -- about 200 sheets deleted and a couple hours later I
gt; gt;gt;gt;gt;gt;gt;finally found out what's going on, though I still don't understand it.
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;I have a custom function which I use to look at a column of randomly organized
gt; gt;gt;gt;gt;gt;gt;numbers and return the average of the largest quot;nquot; of the numbers - quot;nquot; being
gt; gt;gt;gt;gt;gt;gt;specified on another sheet. This function is used once on each of a couple
gt; gt;gt;gt;gt;gt;gt;hundred sheets.
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;Anyhow, if I delete that function from all sheets then when the workbook opens
gt; gt;gt;gt;gt;gt;gt;the event will fire properly. Then I modified the custom function to just
gt; gt;gt;gt;gt;gt;gt;immediately return a value =1 and exit. The event still fails that way though.
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;My immediate kludge concept is to replace the function with a macro that I'll
gt; gt;gt;gt;gt;gt;gt;somehow force to execute (200 times) before I actually use the result from that
gt; gt;gt;gt;gt;gt;gt;cell. I'm certainly open to a better approach though if you have one to
gt; gt;gt;gt;gt;gt;gt;suggest. Or I may spend tomorrow dinking around with the function to see if I
gt; gt;gt;gt;gt;gt;gt;can figure what's strange about it although all it does now is return =1.
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;Thanks...
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;Bill
gt; gt;gt;gt;gt;gt;gt;-------------------------------
gt; gt;gt;gt;gt;gt;gt;Dave Peterson wrote:
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;How are you opening the workbook?
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;File|open or double clicking on its filename in Windows Explorer?
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;Or are you opening the workbook using code in another workbook or a shortcut key
gt; gt;gt;gt;gt;gt;gt;gt;that includes the shift button?
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;If you add:
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt; Private Sub Workbook_Open()
gt; gt;gt;gt;gt;gt;gt;gt; msgbox quot;hi from workbook_openquot;
gt; gt;gt;gt;gt;gt;gt;gt; Call AddButtons
gt; gt;gt;gt;gt;gt;gt;gt; End Sub
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;do you see the msgbox (Maybe it's a problem with addbuttons???).
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;========
gt; gt;gt;gt;gt;gt;gt;gt;If worse came to worse, maybe just moving that workbook_open procedure to a
gt; gt;gt;gt;gt;gt;gt;gt;general module and renaming it to Auto_open() would be a workaround???
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;Bill Martin wrote:
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;gt;Is there any trick to getting the Workbook_Open() event to work? I currently
gt; gt;gt;gt;gt;gt;gt;gt;gt;have a workbook with the following calls in the ThisWorkbook code module:
gt; gt;gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;gt; Option Explicit
gt; gt;gt;gt;gt;gt;gt;gt;gt; Private Sub Workbook_Activate()
gt; gt;gt;gt;gt;gt;gt;gt;gt; Call AddButtons
gt; gt;gt;gt;gt;gt;gt;gt;gt; End Sub
gt; gt;gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;gt; Private Sub Workbook_Deactivate()
gt; gt;gt;gt;gt;gt;gt;gt;gt; Call DeleteButtons
gt; gt;gt;gt;gt;gt;gt;gt;gt; End Sub
gt; gt;gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;gt; Private Sub Workbook_Open()
gt; gt;gt;gt;gt;gt;gt;gt;gt; Call AddButtons
gt; gt;gt;gt;gt;gt;gt;gt;gt; End Sub
gt; gt;gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;gt;It compiles properly and works flawlessly as I switch back and forth between
gt; gt;gt;gt;gt;gt;gt;gt;gt;workbooks, but it never seems to work when I first open a workbook. I can open
gt; gt;gt;gt;gt;gt;gt;gt;gt;two different workbooks that have this code and get no buttons created. Then I
gt; gt;gt;gt;gt;gt;gt;gt;gt;switch between them and the buttons come and go as expected. I'm using Excel97
gt; gt;gt;gt;gt;gt;gt;gt;gt;if that makes any difference.
gt; gt;gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;gt;I've also tried immediately disabling events upon entering Workbook_Open, then
gt; gt;gt;gt;gt;gt;gt;gt;gt;executing the Call and finally enabling events again before exiting but it makes
gt; gt;gt;gt;gt;gt;gt;gt;gt;no difference. It just seems like the event never triggers. Or perhaps I'm
gt; gt;gt;gt;gt;gt;gt;gt;gt;misunderstanding what it should do?
gt; gt;gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;gt;Thanks.
gt; gt;gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;gt;Bill
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;gt;gt;
gt; gt;

--

Dave Peterson