Worksheet_Activate not triggering when workbook opened
Solution 1
In your Workbook_Open
event turn off ScreenUpdating
, activate a different sheet, activate the sheet you want the event to fire, turn ScreenUpdating
back on.
Solution 2
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.
Related videos on Youtube
jaegee
Updated on July 27, 2022Comments
-
jaegee almost 2 years
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?
-
jaegee over 9 yearsThank you so much, that seems to have resolved the issue! Are you able to explain why/how the resolution works? That is, what is the cause of the problem, why did it suddenly eventuate? Thanks again!
-
Mr. Mascaro over 9 years@jaegee, the activate event will not happen if the sheet is the one currently active. It's just a bug in Excel. Also, please mark my answer as correct with the green checkmark.
-
John Joseph over 4 yearsJust underscoring/restating that it's important to change the Activate subroutine declaration from private (the default) to public. e.g. "Private Sub Worksheet_Activate()" should be changed to "Public Sub Worksheet_Activate()" Otherwise, this won't work.
-
Wim over 4 years@JohnJoseph: that's exactly what I wrote in the comments of the Workbook_Open() sub in my answer. The error handler is just there to catch the "Object doesn't support this property or method"-error that would be raised by calling a private or non-existing Worksheet_Activate() routine.
-
John Joseph over 4 yearsyep, I know - that's why I said "underscoring/restating" - it's important but easy to miss. Great answer - it helped me! I up-voted it.