Color palette for individual worksheets

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" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-12E2F3.06150927052006@msnews.microsoft.com...
> 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.
>
> In article <uLGHojSgGHA.4892@TK2MSFTNGP02.phx.gbl>,
> "Mike Hinchcliffe" <mikehinch@gmail.com> wrote:
>
> > 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

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.

In article <e0k8y4YgGHA.1856@TK2MSFTNGP03.phx.gbl>,
"Mike Hinchcliffe" <mikehinch@gmail.com> wrote:

> 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?