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:
Application Ideas - Color Palette Dialog
You can use this to have your users select colors for filling cells, changing font colors, coloring shapes. I actually incorporated this exact dialog box into my CF Shapes Conditional Formatting for Shapes add-in!
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!
Coming Fall 2017
Auto-Import Macros Directly from wellsr.com
Absolutely FREE when you join the waitlist
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow