vba code for hide/unhide tabs based on color

My questions is how to hide/unhide tabs based on tab color. i right clicked on tab and selected view code and inserted the code to change the color of the tab based on a couple cells criteria. what I need is to add code to hide/unhide each sheet when tab color is true.

Be specific. the name of my file is master copy ....... In this file I have approx. 15 sheets, with the tabs having specific name IE labor report, totals ect. I then copy and paste and rename the file for each specific jobname, We are a small const. company, into a shared dropbox file. The file tracks labor and material for each specific job. Not every job will use each tab within the file. Also, sometimes a tab or sheet will not be activated until a later time. IE my materials recap sheet doesn't change colors until an actual invoice for materials is posted, likewise my labor report is not activated until labor is reported.

Any help would be greatly appreciated. I have tried to google this many times. However, I don't not understand code very well and don't understand exactly what info is needed in code.

Thanks
Todd

You can have multiple cases if you want

Please Login or Register  to view this content.

My tabs are green for each one except totals (Red). Can I put this under my other code. Do I type in exactly as you have it wriiten.

Yes. You will need to find out what the color number is for green.

rivate Sub Worksheet_Change(ByVal Target As Range)
If Range("C43").Value > 0 Then
Me.Tab.ColorIndex = 4
Else
Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub

Sub Hide_SheetsbyColor()
Dim ws As Worksheet

For Each ws In Worksheets
Select Case ws.Tab.Color
Case Is = 255 'red
ws.Visible = xlSheetVisible
Case Is = 65535 'yellow
ws.Visible = xlSheetHidden
End Select
Next ws
End Sub
End Sub

Here is what I have. Also how do I get hidden tab back when needed?

Stnkynts was wondering if you had a chance to look at what I have and have a fix. I am posting another tab code I have.


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C40").Value > 0 Then
Me.Tab.ColorIndex = 4
Else
If Range("I44").Value <> 0 Then
Me.Tab.ColorIndex = 4
Else
Me.Tab.ColorIndex = xlColorIndexNone
End If
End If
End Sub

thanks

was wondering if anybody may have a fix for this issue

thanks

The code you provided is part of the Worksheet_Change event which is different than the Subroutine I provided. They cannot be merged together like you have. You would need to insert a new module and add it there. Google search creating a macro in VBA for more information.

In addition, getting the tab back is easy. I just need parameters to determine when the tab should show.

Stnkynts I googled what you said to, but my lack of knowledge doesn't allow me to understand what is needed. as far as getting tab back, I need to be able to enter info on a hidden tab at a later date. IE if I open job file and enter all info I have at that time, then for some reason we order material which is on a hidden tab or enter labor time I would need to be able to retrieve hidden tab make it active based code I sent previously. Would it be easier if I included my whole file so you can see what is going on.

Was wondering if anybody else had an answer to this. Thanks

You can submit your workbook if you want. Just make sure it is very clear what is going on and what you want to have happen.

Originally Posted by Solus Rankin

What is the point of changing the tab color if you're hiding the sheet?

Why don't you just hide the sheet if C40 > 0?

I want the tab to change colors once it becomes active. I may hide the sheet at first, but later on I may need the sheet to be unhidden and activated. Therefore, I would want the tab to change colors.

I have tried to attach workbook by reading the rules section. do not see a button with a paperclip anywhere.

Test copy for VBA request.xlsm

here is example of my file I have input a little info like job name and # the green highlighted tabs have been activated because I either ordered material or going to order material. The totals is red because of the note on the bottom. I would like to hide all the other tabs for use at a later date. I may or may not need them, but would like to be able to unhide them if I need to input info at a later date.

Thanks for you help

I opened up your file it doesn't appear different than mine. This may help

1. open up new job, input all known data at this point
2. if tab is activate with current info it stays activated and all other tabs are hidden
3 at a later time in the day or anytime after job is opened and I want to add data to any of the current or hidden tabs, I want to be able to unhide tabs and make active.

The reason I want to be able to do this is to save time scrolling back and forth across the page or right clicking and finding page. If this is not available then I have what I have.

Thanks Solus

Yes works great. Thanks for you help.

Solus It works great. However, How did you insert the hide/unhide buttons. I would like to maybe move them to the Labor report sheet. Want to test out different places.

Thanks

Also, Currently have some jobs opened and would like to add this feature to those files.

Thanks Solus Works Great. Exactly what I needed.