Workbook_Open() Event

[Bill Martin]

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]

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

[Bill Martin]

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]

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

[Bill Martin]

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

[Bill Martin]

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