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