Sheet Tab Colour (Excel 2007)

Topic

New Reply

Hi,

I am trying to change the tab colour with a macro and the Appl.Dialogs command. Please help…its not working?!

Sub Test()
myC = Application.Dialogs(xlDialogColorPalette).Show
ActiveSheet.Tab.Color = myC
End Sub

How do I get the colour I choose in the dialog to become the tab colour?

TX

Reply | Quote

Replies

Lots of problems:

1. You haven’t declared the variable myC!
2. The result of the Show method of a dialog is True or False, depending on whether the user clicked OK or Cancel. It does *not* return a color or whatever.
3. The xlDialogColorPalette dialog corresponds to the Color tab of the Options dialog. It is not intended to select a color, and hence there is no way to find out which color the user clicked.

Try Excel Developer Tip: A Color Picker Dialog Box from John Walkenbach.

Reply | Quote

Wow…you are strict!!!
This was a test macro…just a sample “Mr Vogelaar…my master and teacher”. I thought it OK not to Dim???

OK, jokes aside… Is there no way to choose a colour and have the Tab become that colour? It seems really harsh to have to create / download a custom user form to do this??? What is the value of accessing the dialogs then?

Reply | Quote

If you have set “Require variable declaration” in the Editor tab of Tools | Options…, *each* new module will automatically start with the line Option Explicit, forcing you to declare all variables.

If you have *not* set “Require variable declaration” in the Editor tab of Tools | Options…, you have been ignoring all my previous warnings!

The xlDialogColorPalette dialog is *not*, I repeat *not* a color picker. It is the equivalent of the Color tab of the Options dialog. The VBA object model for Excel does not expose a general-purpose color picker, so you’ll have to provide it yourself.

Reply | Quote

Tx.

PS: I would *never* ignore your advice.
My Require variable declaration option is on. I just did not copy that part of the module.

Reply | Quote

Let me just ask (from a different angle)…

Is there any way in VBA to get a colour picker? (Besides the link that Hans posted above.)

What about a User Form. Is there a contol that can be added to the toolbox that allows a colour picker to be built onto a user form?

TX

Reply | Quote

Rory’s Autofilter Highlighter Add-in (All) contains a module named basColour with a function ShowColor that lets the user pick a color, using the Windows API function ChooseColorA.

Reply | Quote