Dynamic list of worksheets based on tab colour

JasonBing

New Member
Joined
Aug 6, 2019
Messages
49
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,940
Office Version
2010
Platform
Windows
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.
Rich (BB 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
 

JasonBing

New Member
Joined
Aug 6, 2019
Messages
49
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,940
Office Version
2010
Platform
Windows
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.
 

JasonBing

New Member
Joined
Aug 6, 2019
Messages
49
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,940
Office Version
2010
Platform
Windows
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!
 

JasonBing

New Member
Joined
Aug 6, 2019
Messages
49
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:biggrin:
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,940
Office Version
2010
Platform
Windows
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:biggrin:
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 ....
 

JasonBing

New Member
Joined
Aug 6, 2019
Messages
49
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 #










<tbody>
</tbody>
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,940
Office Version
2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,104
Messages
5,484,700
Members
407,464
Latest member
Halaseh

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top