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,869
    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
    198
    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 10th, 2017 at 11:09 PM.
    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,869
    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
    Board Regular Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    1,034
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 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 09: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 on Win 7

  5. #5
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,869
    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
    Board Regular Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    1,034
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 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 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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com