• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to add a "third" option (2nd color).

Eloise T

Eloise T

Active Member
This VBA macro allows me to toggle a "button" inside each Tab of my workbook which simply changes the color of the Tab I am working in.

The color toggles from red to the default color (off) on the Tab. I use this to remind myself if I've "updated" the contents of that Tab this week or not.

I wish to add a third color (green), so that when I hit the button the first time it becomes red, the second time I hit the button the Tab turns green, and the third time I hit the button it returns to the default color (off). Essentially it becomes a three-way toggle.

Here is the VBA macro that toggles from red to off. How do I change the If/Else to add the second color/third option?

Code: 
Sub Swap_Tab_Color()
  Application.ScreenUpdating = False
  a_tab = ActiveSheet.Name
  a_color = Worksheets(a_tab).Tab.ColorIndex

'  3 = red, 4 = green, 7 = magenta

  If a_color = 3 Then
    a_color = xlNone
  Else
    a_color = 3

  End If
  Worksheets(a_tab).Tab.ColorIndex = a_color
End Sub
THANKS!
MOD Edit: Code Tags added!
 
@Eloise T
1) Control TABs color by Toggle-button's status
2) or something like this ( not tested )
Code: 
Sub Swap_Tab_Color()
  Application.ScreenUpdating = False
  a_tab = ActiveSheet.Name
  a_color = Worksheets(a_tab).Tab.ColorIndex
  select case a_color
    case 3
        a_color = xlnone
    case 4
        a_color = 7
    case xlnone
        a_color = 4
  end select
  Worksheets(a_tab).Tab.ColorIndex = a_color
End Sub
 
Using your "select case" code segment changed the color to magenta, then green, but hitting the button the third time did not turn the Tab color off.

Doing a little research (Discovering the "ElseIF") and experimenting I found that this worked:

Code: 
Sub Swap_Tab_Color()
  Application.ScreenUpdating = False
  a_tab = ActiveSheet.Name
  a_color = Worksheets(a_tab).Tab.ColorIndex

  If a_color = 7 Then     'If it's MAGENTA(7)
     a_color = 4           'Then change it to GREEN(4).
   ElseIf a_color = 4 Then 'If it's GREEN then change it to OFF.
     a_color = xlNone    
  Else:  a_color = xlNone 'If it's OFF then change it to MAGENTA.
     a_color = 7

  End If
  Worksheets(a_tab).Tab.ColorIndex = a_color
End Sub
 
Last edited:
Thank you very much Mr. vletm for this great code
I just edited it a little to get it work properly as desired
Code: 
Sub Swap_Tab_Color()
    Dim a_tab As String
    Dim a_color As Integer
   
    Application.ScreenUpdating = False
        a_tab = ActiveSheet.Name
        a_color = Worksheets(a_tab).Tab.ColorIndex
       
        Select Case a_color
            Case xlNone
                a_color = 3
            Case 3
                a_color = 14
            Case 14
                a_color = xlNone
            Case Else
                a_color = xlNone
        End Select
       
        Worksheets(a_tab).Tab.ColorIndex = a_color
    Application.ScreenUpdating = True
End Sub
 
OMG test this
or as YasserKhalil wrote
Code: 
Sub Swap_Tab_Color()
  Application.ScreenUpdating = False
  a_tab = ActiveSheet.Name
  a_color = Worksheets(a_tab).Tab.ColorIndex
  Select Case a_color
    Case 3
        a_color = 4
    Case 4
        a_color = xlNone
    Case xlNone
        a_color = 3
  End Select
  Worksheets(a_tab).Tab.ColorIndex = a_color
End Sub
 
YasserKhalil said:
Thank you very much Mr. vletm for this great code
I just edited it a little to get it work properly as desired
Code: 
Sub Swap_Tab_Color()
    Dim a_tab As String
    Dim a_color As Integer
  
    Application.ScreenUpdating = False
        a_tab = ActiveSheet.Name
        a_color = Worksheets(a_tab).Tab.ColorIndex
      
        Select Case a_color
            Case xlNone
                a_color = 3
            Case 3
                a_color = 14
            Case 14
                a_color = xlNone
            Case Else
                a_color = xlNone
        End Select
      
        Worksheets(a_tab).Tab.ColorIndex = a_color
    Application.ScreenUpdating = True
End Sub
Click to expand...
Thank you both, vletm and YasserKhalil.

What is the trick so I can highlight and copy so I can paste the code?
 
I didn't get the question well but as I understand
Drag and highlight the lines of the code .. In the excel file press Alt + F11 and from Menu >> Insert >> Module >> Right click and paste (or Ctrl + V from keyboard)
After that return to your worksheet >> press Alt + F8 to see the macros ..Select the macro name Swap_Tab_Color and press Run
 
YasserKhalil said:
I didn't get the question well but as I understand
Drag and highlight the lines of the code .. In the excel file press Alt + F11 and from Menu >> Insert >> Module >> Right click and paste (or Ctrl + V from keyboard)
After that return to your worksheet >> press Alt + F8 to see the macros ..Select the macro name Swap_Tab_Color and press Run
Click to expand...

Thank you for your reply. I moment ago I could not "drag and highlight" the lines of code. Now it's working. I don't understand what changed or why.
 
Last edited:
I'm suffering the same prolem sometimes after updating my browser (Firefox) so I uninstall latest version and installed an older version
 

Yes Eloïse, my question belongs to Yasser after its post upon Firefox !

As I think it's not a concern with webbrowser version …
​
 
@Mr. MarcL
As for me I was using firefox v44.0.2 32bit (on windows 10 64 bit) and after discovering there is v44.0.2 64bit I used it and at this moment the issue happened so I returned ti the 32bit version
 
You must log in or register to reply here.
Back
Top