Color palette for individual worksheets

[Mike Hinchcliffe]

Hi, wonder if someone can help me with this problem. I'm trying to build an
addin that lets users apply one of six different color palettes to
individual worksheets in an excel workbook. It's easy enough to get this
working such that a color palette can be selected for the workbook as a
whole, but I can't figure out how best to make the selection for an
individual worksheet, such that a different palette could be applied to each
worksheet in the workbook.

Any clues?
thanks
Mike

 

[Jim Cone]

Mike,

Each workbook has a single color palette with 56 colors.
If you change any of the colors on the palette then those changes
apply to the entire workbook.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Hi, wonder if someone can help me with this problem. I'm trying to build an
addin that lets users apply one of six different color palettes to
individual worksheets in an excel workbook. It's easy enough to get this
working such that a color palette can be selected for the workbook as a
whole, but I can't figure out how best to make the selection for an
individual worksheet, such that a different palette could be applied to each
worksheet in the workbook.

Any clues?
thanks
Mike

 

[JE McGimpsey]

You could use event macros to change the palettes. For instance, one way
might be something roughly along these lines:

In the ThisWorkbook module of your add-in:

Private Sub Workbook_Open()
Set clsPalette = New PaletteClass
End Sub

In a class module (named PaletteClass):

Public WithEvents oApp As Excel.Application

Private Sub Class_Initialize()
Set oApp = Excel.Application
End Sub

Private Sub oApp_WindowActivate( _
ByVal Wb As Excel.Workbook, _
ByVal Wn As Excel.Window)
If IsMyWorkbook(Wb) Then _
SetPalette ActiveSheet
End Sub

Private Sub oApp_SheetActivate(ByVal Sh As Object)
If IsMyWorkbook(Sh.Parent) Then SetPalette Sh
End Sub

where "IsMyWorkbook" is some method that determines whether the workbook
belongs to (i.e., should be acted on by) the add-in and returns a
boolean True/False.

Each sheet should have a worksheet-level name (here, "jemPalette") that
has a value 1-6 for each palette.

Then in a regular code module (just changing one color, here):

Public clsPalette As PaletteClass

Public Sub SetPalette(ByRef ws As Worksheet)
With ws
Select Case .Names("jemPalette").Value
Case "=1"
.Parent.Colors(3) = RGB(0, 0, 255)
Case "=2"
.Parent.Colors(3) = RGB(0, 255, 0)
' etc.
Case Else
.Parent.Colors(3) = RGB(221, 8, 6)
End Select
End With
End Sub


Note that if more than one window is open for a workbook, the inactive
window will reflect the active window's color (hence the
_WindowActivate() macro).

You'll also need a way for the user to change the value of the name. And
of course, error checking would need to be implemented.

 

[Mike Hinchcliffe]

Great approach - I think this will do it, and actually solve another related
issue I have! Just to clarify, this is going to update the color palette
for the entire workbook when a sheet is activated and it's palette changed,
then when you activate another sheet the whole palette will be switched
again to correspond to the required palette for the new active sheet.

Is this likely to have a hit to performance, or not be that heavy on system
resources?

Many thanks
Mike

 

[JE McGimpsey]

Yes, it changes the workbook palette when a worksheet is activated.

It will have no hit to performance, other than perhaps adding a
negligible delay during the switching of sheets. Since it's only invoked
when a sheet is activated, it doesn't affect calculation at all.