How to colour ONLY the Active Worksheet Tab?

[Leon Lai]

Hello,

How can we color the Active worksheet tab in Excel in green, and ONLY the active tab?

Suppose all tab colors are white.
If I click on Sheet1, the tab must become green.
When I click on Sheet2, this tab becomes green, but tab for Sheet1 should return to white, etc

So, what I want is to color ONLY the active tab. Purpose: To indicate user which is the active worksheet.

The VBA code should be in an add-in, and it changes the tab color of any open worksheet.

So, I use Application Events.
--------

I tried this code, which I put in a Class (myAppEvents).

Code: Select all

Private WithEvents myApp As Application

Private Sub myApp_SheetActivate(ByVal Sh As Object)
myApp.EnableEvents = True
ActiveSheet.Tab.Color = vbGreen
End Sub

-----

What happens?

Each time I click on any tab, it becomes Green.
Fine!
But when I click another tab, the former one does NOT return to white.
So, with repeated clicks, ALL tabs finally become green - which is not what I want.

---------
I also used SheetDeactivate, but it does not work.

Code: Select all

Private Sub myApp_SheetDeactivate(ByVal Sh As Object)
myApp.EnableEvents = True
ActiveSheet.Tab.Color = vbWhite
End Sub

------
Any help much appreciated.

Leon Lai

[HansV]

Use the Sh argument - this is the sheet being activated/deactivated:

Code: Select all

Private Sub myApp_SheetActivate(ByVal Sh As Object)
    Sh.Tab.Color = vbGreen
End Sub

Private Sub myApp_SheetDeactivate(ByVal Sh As Object)
    Sh.Tab.Color =vbWhite
    ' Alternative:
    ' Sh.Tab.ColorIndex = xlColorIndexNone
End Sub

[Leon Lai]

Hello HansV
Thanks a lot for your reply.
Your code works great!
Best Regards,
Leon