Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA - USA
    Posts
    21
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Active sheet tab color

    Hi all,

    Am using Excel 2010.
    Is it possible to change the color of the tab for the active sheet?
    I am thinking like red instead of white.

    Thanks,
    Albert

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    113
    Thanks
    8
    Thanked 15 Times in 14 Posts
    Hi Albert

    That's an interesting question so here may be an answer using event handler macros. If you don't now how to write a macro, do this:

    Save your workbook as an XLSM so you can have macros.
    Go to developer tab and open the VBA editor.
    Double click the ThisWorkbook object in the left pane and write this code in the right pane:

    Private oldindex As Integer

    Sub workbook_sheetactivate(ByVal Sh As Object)
    oldindex = Sh.Tab.ColorIndex
    Sh.Tab.ColorIndex = 3
    End Sub

    Sub workbook_sheetdeactivate(ByVal Sh As Object)
    If oldindex <> 0 Then Sh.Tab.ColorIndex = oldindex
    End Sub

    That SHOULD make the active tab underline in red without changing the colour of any other tabs. I can't say I've tested it very much. Unfortunately it is a characteristic of Excel 2010 that the active tab is only underlined, not coloured all over. If you prefer another colour try different colorindex values other than 3. There's a list here: http://msdn.microsoft.com/en-us/libr...rIndexProperty

    Ian

  3. The Following User Says Thank You to iansavell For This Useful Post:

    abaas (2011-12-08)

  4. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,766
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi,

    This seemed a useful function, so I decided to add it to an Excel 2003 workbook to see how it performed - the code seemed simple enough that I thought it might work there too.

    The problem I ran into is that once the code is added, I can't change the color of a tab (even from no color). When I activated a sheet (after the code was added and compiled), I changed the tab/sheet's color. But once I clicked on another tab, the deactivated sheet's color reverted back to its color from before the change.

    Looking at the code, I can see why the above is so. Not sure if Excel 2003 behaves differently in this regard than 2010 (I have 2010 but didn't test it there since I don't use it much).


    What one would want is someway to know when a sheet tab color changes and update the oldindex but I don't think there's a way to do this (the SheetChange event doesn't seem applicable).


    Does the code work for 2010 even if a tab color is changed after the code has been added?

    Thanks.

    Fred

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,891
    Thanks
    0
    Thanked 82 Times in 78 Posts
    Something like this I guess:
    Code:
    Option Explicit
    
    Private oldindex         As Integer
    Const lTAB_COLOUR        As Long = 3
    Sub workbook_sheetactivate(ByVal Sh As Object)
       oldindex = Sh.Tab.ColorIndex
       Sh.Tab.ColorIndex = lTAB_COLOUR
    End Sub
    
    Sub workbook_sheetdeactivate(ByVal Sh As Object)
       If oldindex <> 0 Then
          If Sh.Tab.ColorIndex = lTAB_COLOUR Then Sh.Tab.ColorIndex = oldindex
       End If
    End Sub
    as long as you don't want to make the tab colour the same as the selected highlight colour.
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •