VBA to Colour Spreadsheet tabs

[Excelorate3305]

Is it possible to automatically colour spreadsheet tabs in a workbook using VBA? I would like to either:

a) set all tabs to white then have the worksheet tab I have selected automatically change to any colour, or
b) have coloured tabs in the workbook then have the worksheet tab I selected appear white or more boldly then how the other tabs appear.

Any assistance greatly appreciated.

 

[igold]

I don't know how you want to use it but here is the simple line of code...

Worksheets("Sheet1").Tab.Color = vbWhite

 

[Micron]

Perhaps like

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
     ws.Tab.Color = RGB(255, 125, 50)
Next

End Sub

You'd probably want to have similar in newsheet event.

 

[igold]

The code that @Micron wrote would have to be placed in the "ThisWorkbook" module for it to trigger automatically when the Workbook is opened...

 

[Fluff]

Another option

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
   Sh.Tab.Color = False
   ActiveSheet.Tab.Color = vbRed
End Sub

This will need to go in the ThisWorkbook module.

 

[Micron]

The code that @Micron wrote would have to be placed in the "ThisWorkbook" module for it to trigger automatically when the Workbook is opened...

I guess I took that for granted as it's the only module that has that event in the drop down list AFAIK.
Using vb constants is OK but you're limited to 8 colours, which is why I went with RGB. I also skipped a) and went with option b), BTW. I suspect that the selected color highlight is theme based, so lighter for dark colours, darker for light colours. Hard to make a dark colour look darker sometimes, so I say go with the theme - but not for me to decide.

 

[igold]

@Micron, yes, you are correct, I just quickly put down something easy, for the OP to get the line of code he needed. For a full range of choice he would be better off with RGB... Then all things considered, I have seen other new posters not realize that certain codes need to go into certain modules to work correctly...

 

[Micron]

I have seen other new posters not realize that certain codes need to go into certain modules to work correctly..

Me too - just yesterday, I think. Sheet event was put into a standard module & then reported as not working so you were right to point that out.

 

[Excelorate3305]

Suggestions do not appear to be working. I have not described my requirement properly.

Assume that I have a workbook with 10 worksheets in it with tabs named "Worksheet 1", "Worksheet 2" etc. through to "Worksheet 10". Also assume that all tab colours are white.

If I select "Worksheet 5" then I want that tab to change colour (other than white) while all the other tabs remain white. If I then select "Worksheet 9" then I want the "Worksheet 9" tab to change colour to what the "Worksheet 5 " tab was and I want the "Worksheet 5" tab to revert back to white..........and so on.

Sorry for any confusion.

 

[igold]

Copy this to your Thisworkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Sh.Tab.Color = RGB(255, 0, 0)
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Sh.Tab.Color = RGB(255, 255, 255)
End Sub