CODEWITHSUNDEEP
excelvba
Han
Han

Reputation: 45

Worksheet_Activate does not run when opening workbook, even if I call sheet.Activate

Currently I have created VBA code in worksheet 1 "Sheet1" as 

  Private Sub Worksheet_Activate ()

So every time I open the worksheet the VBA code will auto run.

But the problem I'm facing now is every time I open the Excel workbook, even though I added the codding in ThisWorkbook, 

 Private Sub Workbook_Open()

     Worksheets("Sheet1").Activate

 End Sub

the worksheet will appear first but the VBA code won't auto run. Every time I need to shift another worksheet then shift back again, then only the VBA code will run, this is very annoying, is there any solution to fix this?

Upvotes: 2

Views: 7088

Answers (2)

Nathan
Nathan

Reputation: 21

Make the Worksheet_Activate Public instead of Private, then run Worksheet("???").Worksheet_Activate within you Workbook_Open code.

Upvotes: 2

user4039065
user4039065

Reputation:

Using the Worksheet.Activate method won't do anything if that worksheet was already active.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Worksheets("Sheet2").Activate
    Worksheets("Sheet1").Activate
    Application.ScreenUpdating = True
End Sub

Make sure that you are passing the activation trigger to another worksheet and back to Sheet1 in order to run the Worksheet_Activate event macro or just run the code from the Workbook_Open sub.

Upvotes: 6

Related Questions