When is the first worksheet activated?

  • Thread starter Thread starter rvExcelTip
  • Start date Start date
R

rvExcelTip

The attached workbook (zipped) contains two empty Worksheets (Sheet1 an
2). I wanted to test when the first WorkSheet (say Sheet1) get
activated, Therefore I added several Event procedures, each containin
a simple Debug.Print statement.

To my surprise the Activate event NEVER fired when opening th
workbook.

The sequence of recorded event was as follows:

WorkBook_open
WorkBook_Activate
WorkBook_Window_Activate
WorkBook_Window_WorkSheet_Sheet1_Activate

The latter statement caused by a WorkSheets("Sheet1").Activate metho
coded in the Window_Activate event.

Even then, the Debug.Print statement in the WorkSheet_Activate event o
Sheet1 never fired!!

However if you now switch to Sheet2, the Sheet1 gets DeActivated a
the next sequence of events clearly shows:

Sheet1_DeActivate
WorkBook_SheetDeactivate
Sheet2_Activate
WorkBook_SheetActivate

So three questions.

- When gets a WorkSheet activated?
- What does it mean to get Activated? (According to MS the Activat
Method Activates a Worksheet!!!)
- Is there somewhere a publication that explains the sequence of event
supposed to take place in excel?
 
Where did you store the event code.

Worksheet event code goes in the worksheets, workbook event code goes in
ThsiWorkbok.

Post the code, we will fix it for you.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A lot of people will not read your message since it contained/pointed to an
attachment.

I know I don't open workbooks--too many threats of malicious code.

But you may want to take a look at Chip Pearson's site:

http://www.cpearson.com/excel/events.htm

He even has a workbook with a bunch of events built in. So you can see them
fire in sequence.
 
Dave, I appreciate your concerns about malicious code in workbook
especially when posted by a "junior" member (but then the forum offer
the posibility). In this case it was probably a greater effort for m
to generate the attachment than it would have been to post the simpl
code as you can observe below.

I have read C. Pearson's article and in a sense it confirms m
observations: Opening a workbook does not seem to activate a worksheet
Check in the article the first example (Opening a workbook) in th
paragraph "Order of Event Procedures". You can see in my first messag
that I have exactly the same order of events (it should!) except that
don't have a previous workbook and that I didn't include th
application object.

You can check for yourself, with the following code (no mor
attachments)

Code for ThisWorkBook:
-Private Sub Workbook_Activate()
Debug.Print "WorkBook_Activate"
End Sub

Private Sub Workbook_Open()
Debug.Print "WorkBook_open"
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Debug.Print "WorkBook_SheetActivate"
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Debug.Print "WorkBook_SheetDeactivate"
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Debug.Print "WorkBook_Window_Activate"
Worksheets("Sheet1").Activate
Debug.Print "WorkBook_Window_WorkSheet_Sheet1_Activate"
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Debug.Print "WorkBook_Window_Deactivate"
End Sub-

Code for Sheet1
-Private Sub WorkSheet_Activate()
Debug.Print "Sheet1_Activate"
End Sub

Private Sub Worksheet_Deactivate()
Debug.Print "Sheet1_DeActivate"
End Sub-

And the same for Sheet2:
-Private Sub WorkSheet_Activate()
Debug.Print "Sheet2_Activate"
End Sub

Private Sub Worksheet_Deactivate()
Debug.Print "Sheet2_Activate"
End Sub-

When opening the Workbook, you clearly see that Sheet1 shows up in th
Active Window. So Sheet1 must have been Activated ??? And why doe
the Worksheets("Sheet1").Activate statement in th
Workbook_WindowActivate event not fire??

C. Pearson (see first table in his article) seems to associat
Activation with "being displayed". So there should have been a
activation event??

The background for my question is (besides trying to understand wha
happens) that I want to do some initialization processing when
WorkSheet is first Activated. So I thought that the logical place t
do this would be the Activate event.

The alternative could be to put that code into the WorkBook_Open event
but then I initialize some worksheets even if they never will b
activated
 
Sometimes the things we think "must" be happening just aren't.

When the Application opens, there is no ActiveWorbook. Therefore when a
workbook opens, the ActiveWorkbook changes, and the Workbook_Activate
event fires.

However, every Workbook has an ActiveSheet, and the status of the
ActiveSheet is saved with the workbook. Worksheet_Activate events fire
only when a new worksheet is activated. Since when the workbook is
opened Sheet1 already is the activesheet, no workbook_SheetActivate or
Sheet1!Worksheet_Activate events are fired.

Now, if you saved the file with Worksheet2 active and included
Worksheets("Sheet1").Activate in the Window_Activate code, you'll see
that the both the Workbook_SheetActivate and Sheet1!Worksheet_Activate
events fire.

one possibility would be to put the Sheet1 related code in a macro in a
regular code module (remember to fully qualify ranges and such), then
call the macro from both the Workbook_Open and the Worksheet_Activate
events.
 
That clearly explains what's happening and settles the mystery. Thank
very much.

Morality:
You cannot rely on the WorkSheet_Activate event alone to initializ
variables that are not saved with the workbook (e.g. list items tha
are set through an array assignment
 
Back
Top