Introduction | Example | Tutorial | Applications
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
Make powerful spreadsheets with our free VBA Developer's Guide Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
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.
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! When you’re ready to take your VBA to the next level, subscribe using the form below.