Introduction | Example | Tutorial | Applications | Comments
Introduction - Color Palette Dialog
Ever wanted to color a cell or fill a range with VBA, but wished your users could select their own colors? I’ll teach you how to make this a reality by showing the Color Palette Dialog Box. With VBA, it’s as simple as calling the xlDialogEditColor dialog.
Example - Color Palette Dialog
Color Active Cell
Sub ColorPaletteDialogBox() Dim lcolor As Long If Application.Dialogs(xlDialogEditColor).Show(10, 0, 125, 125) = True Then 'user pressed OK lcolor = ActiveWorkbook.Colors(10) ActiveCell.Interior.Color = lcolor Else 'user pressed Cancel End If End Sub
Tutorial - Color Palette Dialog
The xlDialogEditColor command accepts as few as 1 argument and as many as 4 meaningful arguments.
The first argument must be an integer from 1 to 56. These 56 integers correspond to the 56 ColorIndex Colors shown in this Infographic. If you only enter one argument, like in the next example, your dialog box will appear on the Standard colors tab and the ColorIndex corresponding to your integer argument will appear.
Sub ColorPaletteDialogBox2() 'ColorIndex Colors Application.Dialogs(xlDialogEditColor).Show (10) End Sub
The 2nd through 4th arguments are RGB values. When you enter the 3 RGB values, your dialog box will appear on the Custom tab with your RGB values selected. Take a look:
Sub ColorPaletteDialogBox3() 'RGB Colors Application.Dialogs(xlDialogEditColor).Show 10, 0, 125, 125 End Sub
Notice the first argument,
Once you select your color and click OK, the color associated with the ColorIndex you entered in your macro - your first argument - changes to whatever color you selected in the color palette. Your new color is stored as a long data type.
In other words, you’re actually changing the color of the ColorIndex integer in your workbook.
To recall or invoke the color you selected, all you have to do is refer to the ColorIndex. In our examples, we changed the color of ColorIndex 10, so here’s an example of how you would grab that color:
ActiveCell.Interior.Color = ActiveWorkbook.Colors(10)
The xlDialogEditColor dialog is just one of many color palettes you can invoke with VBA. Try changing xlDialogEditColor line in the above examples to one of the following to display other palettes:
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.
Application Ideas - Color Palette Dialog
You can use this to have your users select colors for filling cells, changing font colors, coloring shapes.
Anything you would change the color of in VBA, you can display a color palette to do the same thing. It gives your user a sense of control over how your macro behaves. This feeling goes a long way toward satisfying your customer!
I’m afraid that’s all I have for today! I hope you’ll comment below to share how you’ll use this macro!
As always, subscribe to my email list, share this article on social media and follow me on Twitter for more great VBA content. Check out our VBA Q&A community if you still have questions or want to learn more.