Dynamic list of worksheets based on tab colour
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Dynamic list of worksheets based on tab colour
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    49
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic list of worksheets based on tab colour

    I have a workbook with about 16 admin and finance sheets. Then the rest of sheets contain the data for each job, like job cards. These sheets are coloured blue and the admin sheets are coloured green. I have been asked to create another admin sheet with a dynamic list of sheet names with hyperlinks. But they only want the blue sheets to be shown on this list, so they can easily navigate to the job cards to update them.

    I am stumped.

    Any help would be greatly appreciated.

    Thanks

    Jason Bing

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,379
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic list of worksheets based on tab colour

    Create a new sheet with tab name Index. Right-click the tab and choose "view code". Then copy and paste the code below into the white space that appears in the VB Editor window that opens. Save the file with a .xlsm extension. Choose any other tab, then choose the Index tab again. Now whenever you choose the Index sheet it will update automatically with links to the sheets with blue tab color. NOTE: I'm assuming that the blue is vbBlue (R,G,B: 0,0,255). If not change the color in the code to match whatever blue you are using.
    Code:
    Private Sub Worksheet_Activate()
    Dim wSheet As Worksheet
    Dim n As Integer
    Dim calcState As Long, scrUpdateState As Long
    
    calcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    scrUpdateState = Application.ScreenUpdating
    Application.ScreenUpdating = False
    n = 1
    
        With Me
            .Columns(1).ClearContents
            .Cells(1, 1) = "INDEX"
            .Cells(1, 1).Name = "Index"
        End With
        
        For Each wSheet In Worksheets
            If wSheet.Name <> Me.Name And wSheet.Tab.Color = vbBlue Then
                n = n + 1
                    With wSheet
                        .Range("A1").Name = "Start_" & wSheet.Index
                        .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                        SubAddress:="Index", TextToDisplay:="Back to Index"
                    End With
                    
                    Me.Hyperlinks.Add Anchor:=Me.Cells(n, 1), Address:="", _
                    SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
            End If
        Next wSheet
    Application.Calculation = calcState
    Application.ScreenUpdating = scrUpdateState
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    49
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic list of worksheets based on tab colour

    Thank you for your quick reply.

    I did as you instructed. I selected tab colour on the tabs needed and set the r,g,b to 0,0,225.

    When I went back to the INDEX Sheet I get the word INDEX in cell A1. But nothing else happens.

    There is no error message at all is there something else I need to check?

    Thanks again

    Jason Bing

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,379
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic list of worksheets based on tab colour

    Quote Originally Posted by JasonBing View Post
    Thank you for your quick reply.

    I did as you instructed. I selected tab colour on the tabs needed and set the r,g,b to 0,0,225.

    When I went back to the INDEX Sheet I get the word INDEX in cell A1. But nothing else happens.

    There is no error message at all is there something else I need to check?

    Thanks again

    Jason Bing
    As I noted in post#2, the B in RGB is 255 NOT 225.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    49
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic list of worksheets based on tab colour

    Oh wow. I need more coffee. I apologise for that. Thank you soo much fo having the patience to reply. I will sort this out and send some feedback. Once I have it working.

    Thank you

  6. #6
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,379
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic list of worksheets based on tab colour

    Quote Originally Posted by JasonBing View Post
    Oh wow. I need more coffee. I apologise for that. Thank you soo much fo having the patience to reply. I will sort this out and send some feedback. Once I have it working.

    Thank you
    Good luck!
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    49
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic list of worksheets based on tab colour

    Works Perfectly. More coffee consumed and all is well!!

    I have changed the code a bit.

    I have now got three different colour events. I have three sheets called PLANNED, IN-BUILD and COMPLETE. Each job card (or Sheet) has a field to change the status to one of these three mentioned. I put a code in the job card sheets that changes the tab colour depending on the value of this cell. So if the status is PLANNED the tab changes to red and appears as a hyperlink on the PLANNED Jobs Sheet. Works really great. Thanks!! oh and I have changed the link created in the sheets to be in the cell I want it to be, although it is not really needed as the job cards have a return home button.

    I have tried to combine the three different codes into one sheet. (To add a clarifying statement. in altering the code each "STATUS" list appears in different columns now. EG PLANNED in Column B, IN-BUILD in Column D and COMPLETE in column F) The problem is They are all called "Private sub Worksheet_Activate ()" I now understand why but I don't know how to get them to all work on one sheet.

    Here are the three lumps of code running really well on individual sheets.

    CODE 1

    Private Sub Worksheet_Activate()
    Dim wSheet As Worksheet
    Dim n As Integer
    Dim calcState As Long, scrUpdateState As Long

    calcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    scrUpdateState = Application.ScreenUpdating
    Application.ScreenUpdating = False
    n = 2

    With Me
    .Columns(2).ClearContents
    .Cells(2, 2) = "PLANNED"
    .Cells(1, 2).Name = "Index"
    End With

    For Each wSheet In Worksheets
    If wSheet.Name <> Me.Name And wSheet.Tab.Color = vbRed Then
    n = n + 1
    With wSheet
    .Range("A1").Name = "Start_" & wSheet.Index
    .Hyperlinks.Add Anchor:=.Range("A1000"), Address:="", _
    SubAddress:="Index", TextToDisplay:="Back to Index"
    End With

    Me.Hyperlinks.Add Anchor:=Me.Cells(n, 2), Address:="", _
    SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
    End If
    Next wSheet
    Application.Calculation = calcState
    Application.ScreenUpdating = scrUpdateState
    End Sub




    CODE 2

    Private Sub Worksheet_Activate()
    Dim wSheet As Worksheet
    Dim n As Integer
    Dim calcState As Long, scrUpdateState As Long

    calcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    scrUpdateState = Application.ScreenUpdating
    Application.ScreenUpdating = False
    n = 2

    With Me
    .Columns(4).ClearContents
    .Cells(2, 4) = "IN-BUILD"
    .Cells(4, 4).Name = "Index"
    End With

    For Each wSheet In Worksheets
    If wSheet.Name <> Me.Name And wSheet.Tab.Color = vbGreen Then
    n = n + 1
    With wSheet
    .Range("A1").Name = "Start_" & wSheet.Index
    .Hyperlinks.Add Anchor:=.Range("A1000"), Address:="", _
    SubAddress:="Index", TextToDisplay:="Back to Index"
    End With

    Me.Hyperlinks.Add Anchor:=Me.Cells(n, 4), Address:="", _
    SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
    End If
    Next wSheet
    Application.Calculation = calcState
    Application.ScreenUpdating = scrUpdateState
    End Sub





    CODE 3

    Private Sub Worksheet_Activate()
    Dim wSheet As Worksheet
    Dim n As Integer
    Dim calcState As Long, scrUpdateState As Long

    calcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    scrUpdateState = Application.ScreenUpdating
    Application.ScreenUpdating = False
    n = 2

    With Me
    .Columns(6).ClearContents
    .Cells(2, 6) = "COMPLETE"
    .Cells(1, 6).Name = "Index"
    End With

    For Each wSheet In Worksheets
    If wSheet.Name <> Me.Name And wSheet.Tab.Color = vbBlue Then
    n = n + 1
    With wSheet
    .Range("A1").Name = "Start_" & wSheet.Index
    .Hyperlinks.Add Anchor:=.Range("A1000"), Address:="", _
    SubAddress:="Index", TextToDisplay:="Back to Index"
    End With

    Me.Hyperlinks.Add Anchor:=Me.Cells(n, 6), Address:="", _
    SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
    End If
    Next wSheet
    Application.Calculation = calcState
    Application.ScreenUpdating = scrUpdateState
    End Sub


    Is there a way to get all these to run on the one worksheet. The guys only need to look at one sheet to see the jobs in different status then.

    Thanks

    Jason Bing

    PS. I really do appreciate this.

    Thanks

  8. #8
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,379
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic list of worksheets based on tab colour

    Quote Originally Posted by JasonBing View Post
    Works Perfectly. More coffee consumed and all is well!!

    I have changed the code a bit.

    I have now got three different colour events. I have three sheets called PLANNED, IN-BUILD and COMPLETE. Each job card (or Sheet) has a field to change the status to one of these three mentioned. I put a code in the job card sheets that changes the tab colour depending on the value of this cell. So if the status is PLANNED the tab changes to red and appears as a hyperlink on the PLANNED Jobs Sheet. Works really great. Thanks!! oh and I have changed the link created in the sheets to be in the cell I want it to be, although it is not really needed as the job cards have a return home button.

    I have tried to combine the three different codes into one sheet. (To add a clarifying statement. in altering the code each "STATUS" list appears in different columns now. EG PLANNED in Column B, IN-BUILD in Column D and COMPLETE in column F) The problem is They are all called "Private sub Worksheet_Activate ()" I now understand why but I don't know how to get them to all work on one sheet.


    PS. I really do appreciate this.

    Thanks
    You are welcome - thanks for the reply. I suspect all three of your codes can go on one sheet, but without seeing more detail, I can't be sure. I know you know exactly what you want to do and exactly where you want links to be placed, but recognize that I have no clue about these things and have no idea what your workbook and worksheets look like ....
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    49
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic list of worksheets based on tab colour

    This is true. I see your point

    Here is what I am trying to do. I have a macro that creates a new sheet from a template. They activate the cell with the job number on it and then this macro takes the information from a master list, creates a new sheet named by the active cells job number, then populates the new sheet from the information on the master list. Effectively creating a job card. As the job progresses the guys update the sheet pertaining to each job. I have a macro on the sheet template now that cycles a cell through three job statuses. PLANNED, IN-BUILD and COMPLETE. The sheet colour updates dynamically to vbRed for PLANNED, vbYellow for IN-BUILD and vbGreen for COMPLETE. I am trying to create a master job sheet so the guys can quickly find a job. So what I am after specifically is a sheet that has links to all the job sheets. This sheet I'm after needs the status title in row 2 (Hence n=2 in my code above) and the tree job statuses (PLANNED, IN-BUILD and COMPLETE) in columns B D and F respectively. I then want a link for each sheet with the status under each status title.

    This is what I am after

    (Column B)
    (Column D) (Column F)
    (Row2) PLANNED IB-BUILD COMPLETE
    LINK TO JOB # LINK TO JOB # LINK TO JOB #
    LINK TO JOB # LINK TO JOB # LINK TO JOB #
    LINK TO JOB # LINK TO JOB # LINK TO JOB #
    LINK TO JOB # LINK TO JOB # LINK TO JOB #
    LINK TO JOB # LINK TO JOB # LINK TO JOB #
    LINK TO JOB # LINK TO JOB # LINK TO JOB #

    My thought originally was to have this dynamically update via TAB colour, so the guys could quickly navigate to each job card

    I hope this makes sense.

    Regards

    Jason Bing

  10. #10
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,379
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic list of worksheets based on tab colour

    So, for any "job card" (sheet) which cell is used to report the job status?

    It seems to me that it might be easier to set up what you want on a single sheet by simply looking at all job card sheets to see what value that cell holds and provide a link to it in the appropriate column. No need to even look at the sheet's tab color. This would run automatically whenever the dynamic links sheet is activated.

    If you can give me the "status cell" address on your job card template I can try to write something for you as time permits.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

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
  •