Worksheet.Activate does not trigger the Worksheet_Activate event

user_12905084 0
Viewed 135

I created macros in vba but they don't work.

In the ThisWorkbook code I wrote:

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

In the code of Sheet1 (Sheet1) I wrote:

Private Sub Worksheet_Activate()
    MsgBox ("hello")
End Sub

but when I open the file nothing happens ...

vba excel
2 Answers

If Sheet1 is already active when you open the workbook, then

Worksheets("Sheet1").Activate

does exactly nothing. Because you cannot activate what is already active.

Test it by adding 2 Sheets Sheet1 and Sheet2. Then select Sheet2 save the file and close it. Now open it again, it will run Worksheets("Sheet1").Activate and this will trigger the Worksheet_Activate properly.

Also note that MsgBox ("hello") should be without parenthesis as it does not return a value to a variable: MsgBox "hello"

An alternative solution is:

Write in a module:

Public Sub Sheet1_Activate()
     MsgBox "Sheet1 Activate"
End Sub

In Sheet1 write:

Private Sub Worksheet_Activate()
    Sheet1_Activate
End Sub

And in ThisWorkbook write:

Private Sub Workbook_Open()
    If ThisWorkbook.ActiveSheet.Name = "Sheet1" Then
        Sheet1_Activate
    Else
        ThisWorkbook.Worksheets("Sheet1").Activate
    End If
End Sub

The idea is to have a new procedure Sheet1_Activate that takes the actual code and is triggered by the Worksheet_Activate as well as by the Workbook_Open in case the sheet is already the active sheet.

user_32196130 
Sub auto_open()
   MsgBox "HueHueHue"
End Sub

You can't activate the same thing twice.

user_110568490
Related