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?

Old post, but if someone looks for this, a recommended reading is [this article](https://pixcels.nl/events-in-workbooks/) — Ricardo Diaz, Jan 10 '20 at 22:48

score 12 · Answer 1 · edited May 23 '17 at 12:30

12

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.

edited May 23 '17 at 12:30

Community

1
1

answered Sep 16 '16 at 09:19

Wim

125
1
7

Just 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. – John Joseph Feb 03 '20 at 20:01
@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. – Wim Feb 05 '20 at 10:00
yep, 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. – John Joseph Feb 06 '20 at 18:05

score 2 · Accepted Answer · answered Feb 04 '15 at 22:17

2

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.

answered Feb 04 '15 at 22:17

Mr. Mascaro

2,693
1
11
18

Thank 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! – jaegee Feb 05 '15 at 01:12
1

@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. – Mr. Mascaro Feb 05 '15 at 13:41

score 0 · Answer 3 · edited Jul 25 '21 at 20:53

0

This setting might also be in play:

Application.ScreenUpdating = true

edited Jul 25 '21 at 20:53

Ole Pannier

3,208
9
22
33

answered Jul 25 '21 at 04:46

RayInAZ

1

Worksheet_Activate not triggering when workbook opened

3 Answers3