Quick Jump
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

VBA Color Palette Dialog Box with ColorIndex

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

VBA Color Palette Dialog Box with RGB

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)

Fill ActiveCell with VBA Color Palette

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. 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!


Comments

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 Google+ and Twitter for more great VBA content.


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


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.