VBA: Run Macro When Sheet Changes

[BIGTONE559]

I currently have code that runs when it's selected however, i would like for the code to run once the sheet is changed.

e.g. when the activesheet is Sheet(1) i would like for it to run once the tab for Sheet(2) is pressed. . . as of now. . the code runs once the sheet is selected. e.g. Sheet(1) is the active sheet when Sheet(2) is clicked it runs on sheet(2).

code:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim aSh As Worksheet
Dim aShRange As Range
Set aSh = ActiveSheet

Set aShRange = aSh.Range("C3")

If Not IsNumeric(Application.Match(Sh.Name, Array("HExport"), 0)) Then

    If aShRange.Value = "" Then
    aSh.Name = aSh.Index
    Else
    aSh.Name = aShRange.Value
    End If
End If


End Sub

As always thanks

Cheers!

 

[BIGTONE559]

Try

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim aShRange As Range
Set aShRange = Sh.Range("C3")
If Not IsNumeric(Application.Match(Sh.Name, Array("HExport"), 0)) Then
    If aShRange.Value = "" Then
    aSh.Name = aSh.Index
    Else
    Sh.Name = aShRange.Value
    End If
End If
End Sub

Perfect. What change did you make? it worked perfectly!

 

[BIGTONE559]

WAIT!!! i just noticed something trippy. . .

Once i click sheet(2) it does change sheet(1) to the appropriate name. However, If Sheet 2 does not have a value in C3. And i Click Sheet(1) it changes the name of Sheet(1) to the index. If i do have a value in Sheet(2) and i click Sheet(1) it keeps the value that is in Sheet(1).Range(C3)

 

[BIGTONE559]

WAIT!!! i just noticed something trippy. . .

Once i click sheet(2) it does change sheet(1) to the appropriate name. However, If Sheet 2 does not have a value in C3. And i Click Sheet(1) it changes the name of Sheet(1) to the index. If i do have a value in Sheet(2) and i click Sheet(1) it keeps the value that is in Sheet(1).Range(C3)

bump