Restrict Colour selection in Excel 3

[SteveMersereau]

Is it possible to use code to restrict the colours a user can choose for font / cell bg within Excel...?

A client wants to allow only corporate colours in a workbook.

If impossible, any thoughts welcome.

Steve
"And all of this is not imagination

 

[fredericofonseca]

Personally I would tell the client it is impossible to do for ALL workbooks.

If you wish to do this just on particular copybooks then yes it is possible through VBA.

This is done by placing specific code on certain events of the workbook/worksheet such as Workbook_SheetChange or Worksheet_Change.

Here it is possible to verify what was done and if colors where changed they can be changed back to what they were.





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[TonyJollans]

Hi Steve,

In an Excel workbook you are restricted to a colour palette of 56 colours. You can change what those colours are but you can only use 56 and all colouring is held internally as a ColorIndex value (an index into the palette). If the palette is changed so that colour number 1 changes from red to blue then all red in that workbook will change to blue.

You cannot (easily) limit the colours any user chooses to set up and use but with code you could, whenever a workbook was opened, set up the palette so that it contained only your corporate colours. This would not stop people using any colour they chose but it would effectively negate their action. So you can set up an environment where it is easy to use corporate colours and extremely awkward to use other ones - assuming you mostly have good corporate employees I would think that more or less meets your need.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[SteveMersereau]

Thanks for both these posts.

Tony, would you give me a pointer to setting up a palette please....

Thanks!

Steve
"...it's just me, putting my hand on my heart

 

[TonyJollans]

Hi Steve,

To do it manually (and rather slowly) go to Tools > Options > Color tab.
To do it in code ...

[blue]With ActiveWorkbook
    .Colors(1) = RGB(51, 51, 51)
    .Colors(2) = RGB(51, 51, 255)
    etc. 
End With[/blue]

As I said, every workbook has its own palette so you may want to reset it (or at least check it) every time a workbook is opened. Alternatively you could look at changing the default template so that new workbooks are created with your palette and existing ones with whatever they have.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[PHV]

Have a look at the Colors property of the Workbook object
and at the menu Tools -> Options -> Color tab.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886