CODEWITHSUNDEEP
excelvba
J.schmidt
J.schmidt

Reputation: 719

Excel VBA: call sub when change to another worksheet

Pretty basic question. I'm looking for a sub which will be triggered when the user changes the active worksheet to a certain worksheet (calles worksheet3).

Doing some research in the internet could not help me, unfortunately.

The event sub I am looking for should be very similar to the workbook_open sub. Obviously it is triggered everytime the workbook opens. So my question: is there a similar event sub which is triggered when I open a certain worksheet? Kind of like worksheet3_open?

Upvotes: 0

Views: 1407

Answers (3)

JNevill
JNevill

Reputation: 50034

You can use the Worksheet_Activate() event in the Worksheet3 code page.

  1. Choose your worksheet by double clicking in the PRoject window:

    enter image description here

  2. From the drop down at the top, choose "Worksheet"

    enter image description here

  3. From the second drop down just to the right choose "Activate".

    enter image description here

You'll have a new subroutine created called Worksheet_Activate() that will fire every time that tab is activated by the user.

You can then call your subroutine inside of that code so it runs as well.

Upvotes: 2

Nathan_Sav
Nathan_Sav

Reputation: 8531

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

or 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

Nothing on the net????? :)

Upvotes: 1

tk78
tk78

Reputation: 957

Place the following code under your workbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    If Sh.Name = "worksheet3" Then
        YourSub
    End If

End Sub

Upvotes: 0

Related Questions