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

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


"Mike Hinchcliffe" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
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

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 <(E-Mail Removed)>,
"Mike Hinchcliffe" <(E-Mail Removed)> 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

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" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> 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 <(E-Mail Removed)>,
> "Mike Hinchcliffe" <(E-Mail Removed)> 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 <(E-Mail Removed)>,
"Mike Hinchcliffe" <(E-Mail Removed)> 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?