VBA to Hide Worksheets Based on Coloured Tabs

[chingching831]

Hi there,

I have a code to hide worksheets based on specific RGB colours. But I am not sure why it isn't working ...

Sub HideColouredTabs()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    If ws.Tab.Color = RGB(191, 191, 191) Then
        ws.Visible = xlSheetHidden
    End If

Next ws

End Sub

 

[Jaafar Tribak]

But I am not sure why it isn't working ...

What happens exactly? Do you get any errors? and if so, in which line?

 

[chingching831]

I don't get any errors, but the sheet doesn't hide after I run the code

 

[Alex Blakenburg]

Maybe with the sheet you want to hide active, run the Sub below and check that the RGB that prints to the Immediate window matches your expected RGB.

Sub GetTabColour()

    Dim strRGB As String
    strRGB = getRGB2(ActiveSheet.Tab)
    Debug.Print strRGB

End Sub

Function getRGB2(rcell_tab) As String
    Dim C As Long
    Dim R As Long
    Dim G As Long
    Dim B As Long
    
    Select Case TypeName(rcell_tab)
    Case "Tab"
        C = rcell_tab.Color
    Case "Range"
        C = rcell_tab.Interior.Color
    Case Else
        Exit Function
    End Select
    R = C Mod 256
    G = C \ 256 Mod 256
    B = C \ 65536 Mod 256
    getRGB2 = "R=" & R & ", G=" & G & ", B=" & B
End Function

 

[chingching831]

What does the 256 and 65536 stand for?

Maybe with the sheet you want to hide active, run the Sub below and check that the RGB that prints to the Immediate window matches your expected RGB.

Sub GetTabColour()

    Dim strRGB As String
    strRGB = getRGB2(ActiveSheet.Tab)
    Debug.Print strRGB

End Sub

Function getRGB2(rcell_tab) As String
    Dim C As Long
    Dim R As Long
    Dim G As Long
    Dim B As Long
   
    Select Case TypeName(rcell_tab)
    Case "Tab"
        C = rcell_tab.Color
    Case "Range"
        C = rcell_tab.Interior.Color
    Case Else
        Exit Function
    End Select
    R = C Mod 256
    G = C \ 256 Mod 256
    B = C \ 65536 Mod 256
    getRGB2 = "R=" & R & ", G=" & G & ", B=" & B
End Function

 

[Alex Blakenburg]

The function converts the long integer color number in VBA to the RGB color coding.
Ref: Determining the RGB Value of a Color
If you really want to get more into the mechanics you can try this:

Converting VBA Long Color Values to RGB

The VBA RGB() function converts red, green, and blue values into a VBA long integer color value. This simple function works in the other direction.

nolongerset.com

For our purposes you just need to run it to see if your tab color really is RGB(191, 191, 191), so that it meets your if statement criteria.
PS: RGB(191, 191, 191) = 12566463

 

[chingching831]

I found that the code can be run successfully if it's added in the module of my current workbook (green arrow), but not my usual workbook (red arrow) that stores all the VBA Codes

 

[Alex Blakenburg]

ThisWorkbook refers to the workbook that the code is in. If you put the code in your personal workbook change it to ActiveWorbook.

 

[chingching831]

I see..thank you!!