Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Calling and using the color picker with VBA

Color Picker VBA

When building Excel-based tools and applications it is often useful for the user to select a color.  Whilst it is possible to build a custom UserForm, Excel’s native Color window is more than suitable for most circumstances.

Excel’s native color picker:

Color Picker Standard Color Picker Custom

To show the Color window, you might think we could use the Macro Recorder, but it doesn’t quite give us what we need.  The recorded code will show the colors selected, but not the action of displaying the Colors window.

Recorded macro code – color picker will not display:

 With Selection.Interior
     .Pattern = xlSolid
     .PatternColorIndex = xlAutomatic
     .Color = 3166746
     .TintAndShade = 0
     .PatternTintAndShade = 0
 End With

This post provides the VBA code required to call the Colors window and apply the user’s selection.

Using the Edit Color Dialog Box

The VBA code to call the Color window is straightforward.

Each window in Excel has it’s own Dialog setting which can be called.  Typing Application.Dialogs( into the Visual Basic Editor will display the list of available dialog boxes.

Color Picker - DialogBox Options

Any of these windows can be called.  The code below will open the Add-In Manager window.


To call the Color Window we would reference  xlDialogEditColor instead of xlDialogAddinManager.

The xlDialogEditColor option does require one argument.  There are 56 color positions available in Excel; when calling the Color window it is necessary to state the which color position to be edited.  The code below shows how to edit the twelfth color position.

Application.Dialogs(xlDialogEditColor).Show (12)

Using a position number higher than 56 will result in an error:

Color Picker - Color Position > 56

It is acceptable to provide the single color position argument, but it may be better to set the default color value when the window opens.

Application.Dialogs(xlDialogEditColor).Show 1, 26, 82, 48

The code above sets color position 1 to the color with RGB value of

  • Red: 26
  • Green: 82
  • Blue: 48

That color is a dark green (just in case you were wondering).

The Color window has two tabs, Standard and Custom (see the images at the top of this article).  If the RGB color code matches a color from the Standard tab the Standard tab will display, else the Custom tab will display.

Full VBA example

The code below, which should be used in a standard Module will:

  • Obtain the color code of a cell with the Named Range of RGBColor
  • Assign the color code to a variable called FullColorCode
  • Use formulas to split the full-color code into it’s Red, Green and Blue components
  • Display the Edit Color Dialog Box, setting the default to the Red, Green and Blue values above
  • On clicking OK, the selected color is assigned to the FullColorCode variable
  • The cell named RGBColor is updated with the new color code from the FullColorCode variable.
Sub ColorDialog()

'Create variables for the color codes
Dim FullColorCode As Long
Dim RGBRed As Integer
Dim RGBGreen As Integer
Dim RGBBlue As Integer

'Get the color code from the cell named "RGBColor"
FullColorCode = Range("RGBColor").Interior.Color

'Get the RGB value for each color (possible values 0 - 255)
RGBRed = FullColorCode Mod 256
RGBGreen = (FullColorCode \ 256) Mod 256
RGBBlue = FullColorCode \ 65536

'Open the ColorPicker dialog box, applying the RGB color as the default
If Application.Dialogs(xlDialogEditColor).Show _
    (1, RGBRed, RGBGreen, RGBBlue) = True Then

    'Set the variable RGBColorCode equal to the value
    'selected the DialogBox
    FullColorCode = ActiveWorkbook.Colors(1)
    'Set the color of the cell named "RGBColor"
    Range("RGBColor").Interior.Color = FullColorCode

    'Do nothing if the user selected cancel

End If

End Sub

Adapting the code

The example above uses a worksheet cell to hold the color.  It is possible to adapt this technique with any objects with a .Color property.  This is not just restricted to worksheets, but to many, many objects within the VBA object model.

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

5 thoughts on “Calling and using the color picker with VBA

    • Excel Off The Grid says:

      Just an idea…

      If you create your own color palette (Page Layout -> Themes -> Colors). You could then load the palette into each workbook using something like the following:

      ActiveWorkbook.Theme.ThemeColorScheme.Load ("C:\Program Files (x86)\Microsoft Office\Root\Document Themes 16\Theme Colors\Blue.xml")

Leave a Reply

Your email address will not be published. Required fields are marked *