Disable Changing Tab Color?

[ptmuldoon]

I have a workbook that has specific tab colors for each group/section in the workbook.

And I find my staff are always changing those colors on my when they are not suppose to. Is there a way to disable the the changing of a worksheet color, and give them a msgbox saying sorry, you can't change the color?

Would that fall under a workbook_sheetchange event?

[mikeTRON]

You can protect the workbook and then they cannot change the color.
Or you can simply let them do whatever nonsense they are going to do then have a macro that loops through all of the tabs and colors them accordingly (or you can put that in the workbook to run on OPEN).

[ptmuldoon]

I wish i could protect the workbook, but there is to many reasons for needing to accessible to everyone.

The 2nd approach may be the better way. I actually already have script that will change the tab colors based on a value of cell, ie, open, closed. And I could probably just check the value of each cell on each sheet and then set the correct color when the workbook opens.

I was just hoping i could actually disable a user from changing the color to something different.

[mikeTRON]

Keep in mind protecting the workbook doesn't protect the SHEET.

[ptmuldoon]

Thanks.

But I still need to let the user be able to add a new worksheet as needed. Just not be able to actually change the color of a 'default' worksheet.

But I think I may have something that works. If I use a worksheet_deactivate event. If the user changes the Tab color, I can just force change it right back based on cell value within the workbook/worksheet.

Please Login or Register  to view this content.