If Statement Assessing Worksheet Tab Color

Results 1 to 6 of 6

Thread: If Statement Assessing Worksheet Tab Color

  1. #1
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,873
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If Statement Assessing Worksheet Tab Color

     
    I am trying to create an if statement that will execute code if a particular worksheet tab is any color other than red.
    I have tried this ...
    Code:
    If Worksheets("Master").Tab.color <> vbRed Then
    but is errs with a "Subscript out of range" error with that line though.

    The worksheet "Master" exists in the active workbook. It's tab is not red, so the code following the if statement should be execute.

    Please help?

  2. #2
    Board Regular ttray33y's Avatar
    Join Date
    Nov 2015
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Statement Assessing Worksheet Tab Color

    Quote Originally Posted by Ark68 View Post
    I am trying to create an if statement that will execute code if a particular worksheet tab is any color other than red.
    I have tried this ...
    Code:
    If Worksheets("Master").Tab.color <> vbRed Then
    but is errs with a "Subscript out of range" error with that line though.

    The worksheet "Master" exists in the active workbook. It's tab is not red, so the code following the if statement should be execute.

    Please help?
    not tested tho.

    try :
    Code:
    Sub test()
    
    Dim tsh As Worksheet
    Dim tCol As String
    
    
    Set tsh = ActiveWorkbook.Sheets("Master")
    tCol = vbred
    
    
        If tsh.Tab.Color <> tCol Then
            With tsh
                .Tab.Color = tCol
            End With
        End If
        
    End Sub
    Last edited by ttray33y; Aug 11th, 2017 at 12:09 AM.
    Please hit the Like or Thank button if you find my answer helpful.

  3. #3
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,873
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Statement Assessing Worksheet Tab Color

    Hello ttray33y,

    My apologies for taking so long to acknowledge your help. Only now have I gotten around to work on this issue.
    I do appreciate the effort! Unfortunately, I still get the same error at the same point with your code. Other than setting the worksheet object and the vbcolor to variables I see little difference to what I had originally used.

    I'm still searching for a solution ... anyone?

    Here is some code ... ttray's contribution in purple, my original in blue.
    Code:
    ws_name = "WS " & format(ws_vh.Range("B17"), "dd-mmm-yy") & ".xlsx"
            If Dir(path_name & ws_name) <> "" Then '.tglb_wpl_crtt.BackColor = RGB(0,153,211)
                Workbooks.Open (path_name & ws_name)
                Set wb_name = Workbooks(ws_name)
                wb_name.Windows(1).Visible = False
                bt = 0
                With wb_name
                    tCol = vbRed
                    If Worksheets("Master").Tab.color <> tCol Then  '<--- Subscript out of range.
                        .tglb_master.Enabled = True
                        .tglb_master.BackColor = RGB(0, 153, 211)
                        bt = bt + 1
                    End If
                    
                    If Worksheets("CUE").Tab.color <> vbRed Then '<--- Subscript out of range.
                        .tglb_cue_ws.Enabled = True
                        .tglb_cue_ws.BackColor = RGB(0, 153, 211)
                        bt = bt + 1
                    End If
          ...

  4. #4
    MrExcel MVP Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,777
    Post Thanks / Like
    Mentioned
    81 Post(s)
    Tagged
    3 Thread(s)

    Default Re: If Statement Assessing Worksheet Tab Color

    I suspect that you are missing a . from infront of the worksheet ie
    Code:
    If .Worksheets("CUE").Tab.color <> vbRed Then
    Also you have a lower case c on color is that in your actual macro?
    Because it would normally be converted to an upper case C
    Last edited by Fluff; Aug 13th, 2017 at 10:38 AM. Reason: added the also
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  5. #5
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,873
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Statement Assessing Worksheet Tab Color

    Hello Fluff ...
    That was a correct suspicion! The period solved the problem indeed.

  6. #6
    MrExcel MVP Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,777
    Post Thanks / Like
    Mentioned
    81 Post(s)
    Tagged
    3 Thread(s)

    Default Re: If Statement Assessing Worksheet Tab Color

      
    Glad to help & thanks for the feedback
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com