Worksheet_Activate not triggering when workbook opened

Question

Two days ago my code to populate ActiveX combo boxes in my Excel sheets stopped functioning when I open the document. I have since discovered that the Worksheet_Activate() no longer triggers when I open sheets.

Now even if I create a simple workbook with only the following code in Sheet 1, it doesn't trigger when I open the workbook.

Private Sub Worksheet_Activate()
   MsgBox ("Worksheet has been activated")
End Sub

However, if I click on another tab and click back to the sheet containing the code, it does trigger.

I have tried playing with adding Application.EnableEvents = True and Worksheets("Sheet1").Activate to the Workbook_Open (which does trigger) but still no luck.

We're running Excel 2010 and the same problem is occurring on my colleagues' machines as well. Any ideas?

Answer 1

I know this is an older question, but there is no need to first activate another worksheet and then re-activate the one you want:

Private Sub Workbook_Open()
    ' Bug in Excel:
    ' The Worksheet_Activate event does not fire for the sheet that is active
    ' when the workbook is opened, so call it explicitely. Make sure that
    ' Worksheet_Activate() is declared as Public.
    ' Ignore ActiveSheets without (Public) Worksheet_Activate()
    On Error Resume Next
    Call ActiveSheet.Worksheet_Activate
    On Error GoTo 0
End Sub

It's also not good practice to reference a worksheet by Name like in Worksheets("Sheet1"), unless you have a very good reason to do so. Better is to use the CodeName. For details, see this post.