CODEWITHSUNDEEP
excelvba
Ans
Ans

Reputation: 1234

Excel vba: detect worksheet switch: when another worksheet gets selected

Suppose I have a workbook with two worksheets: Sheet1 and Sheet2. I want a message to appear when a user goes from Sheet2 back to Sheet1.

I'm not sure how to approach it - so far I've only been meddling with Worksheet_Change sub, but the problem doesn't seem like something that could be solved inside that sub. Right now, I can only think of setting some global variable 

 Dim previousWorksheet As Variant
 Set previousWorksheet = ActiveSheet.Name

And then checking what sheet is the active one:

If previousWorksheet = "Sheet2" And ActiveSheet.Name = "Sheet1" Then
   MsgBox("DETECTED")

End If

But what would trigger that code, I don't know.

What is the best way to accomplish it?

Upvotes: 0

Views: 1553

Answers (2)

DisplayName
DisplayName

Reputation: 13386

Suppose I have a workbook with two worksheets: Sheet1 and Sheet2

then to get a message

when a user goes from Sheet2 back to Sheet1

you could simply go:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = "Sheet1" Then MsgBox "The switch"
End Sub

Upvotes: 1

Ans
Ans

Reputation: 1234

This is what worked for me (inside ThisWorkbook module):

Option Explicit

Dim previousWorksheet As String


Private Sub Workbook_open()
    previousWorksheet = ActiveSheet.Name
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    MsgBox ("changed")

    If ActiveSheet.Name = "Sheet1" And previousWorksheet = "Sheet2" Then
        MsgBox ("the switch")

    End If

    previousWorksheet = ActiveSheet.Name

End Sub

Upvotes: 1

Related Questions