Sheet activate question

[Robert Crandal]

I have 5 sheets that each process "Worksheet_Activate()". When
my workbook first opens, Sheet1 is automatically selected by
default as the first active sheet, however it does not receive any
"Activate" event when the workbook is first loaded.

What is a good way to ensure that the default activated sheet's
"Worksheet_Activate()" handler is called??

thank you!

 

[marcus]

Hi Robert

Just put something like this in the ThisWorkbook module

Private Sub Workbook_Open()
Sheets("Sheet1").Activate
End Sub

Then when sheet1 is activated the worksheet_activate code will run on
that sheet.

Take care

Marcus

 

[Robert Crandal]

Hi marcus,

I tried that, but it doesn't work. Since Sheet1 is automatically
active upon Workbook_Open, it does NOT get sent
an activated message, so Workbook_Open() does not get called.

 

[keiji kounoike]

How about this one?

Private Sub Workbook_Open()
Application.DisplayAlerts = False
Worksheets.Add
ActiveSheet.Delete
Worksheets("Sheet1").Activate
End Sub

Keiji

 

[Peter T]

Change Private to Public Sub Worksheet_Activate()

Private Sub Workbook_Open()

Set ws = Sheet1 ' or Worksheets("Sheet1")

On Error GoTo errH
If ws Is ActiveSheet Then
Call ws.Worksheet_Activate
End If

Exit Sub
errH:
If Err = 438 Then
Debug.Print ws.Name; "_Worksheet_Activate doesn't exist " & _
"or is Private"
Else
Debug.Print Err.Description
End If

End Sub

If you want to call the activate event on any sheet that happens to be
active as the workbook opens start with -

ws = activesheet

There are probably better ways to acheive the same overall objective

Regards,
Peter T