Show Color Palette Dialog Box with VBA xlDialogEditColor

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 macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

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, 10, has no impact on the displayed dialog box. Where does the first argument come into play? Let me explain:

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.Dialogs(xlDialogColorPalette).Show
• Application.Dialogs(xlDialogPatterns).Show

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.