Color Picker Dialog Excel VBA Macro

Microsoft Excel has a broad choice of dialogs available (file dialogs, color and formatting dialogs, etc.). In this post we see how to launch the color picker dialog in Excel using VBA macros. The dialog prompts the user to pick a color that can be used to format the font, interior, or borders of a cell or range later in the macro. The picked color is saved to memory and can be used at any time. 

Macro code:

 
  Sub PickColor()
 
  Dim objDialog As Dialog, colorCode As Long
 
  Set objDialog = Application.Dialogs(xlDialogEditColor)
 
  If objDialog.Show(12) = True Then  'or .Show(12, r, g, b)
      colorCode = ActiveWorkbook.Colors(12)
      ActiveCell.Interior.Color = colorCode
  End If
 
  End Sub
 

 

Macro explained:

• First we declare an object variable that is used to store the Dialog object. Another variable (colorCode) declared as Long stores the selected color.
• The next line sets the dialog to the object variable (objDialog) using the Dialogs property of the Application object. Dialogs accepts a parameter that specifies the type of dialog and can be one of the constants in the XlBuiltInDialog enumeration. See all Excel enumerations in this other page.
• The method “Show” displays the dialog. Depending on the dialog type, the method accepts a different set of parameters. For the color dialog used in the example above (xlDialogEditColor), the first argument is an integer from 1 to 56, which corresponds to the 56 ColorIndex colors for a workbook. Values from 1 to 10 show the standard palette and values after 10 show the customer palette (like picture above).
• Optionally, the method Show accepts three other parameters to determine the red, green and blue levels of the default color in the dialog. When those are specified, the method always shows the custom palette and selected the specified RGB color.
• The conditional statement along the Show method checks whether the user clicked OK (value True) to proceed with the macro. The value is False if user clicks Cancel.
• When clicking OK, the selected color is assigned to the workbook colors palette; more specifically, it is assigned to the specified color index (12 in the example above). We can then save it as a Long number to a variable (colorCode) that can be used later in the macro.
• Finally, we can use that color to format the font, interior, or borders of a cell or range, the color of a worksheet tab, color of shapes, etc. The code above formats the interior color of the active cell with the selected color.

As a practical example, the picture above shows a work schedule where shifts can be highlighted in a given color using the dialog picker to choose the color for each shift.

This is how to show a Color Picker Dialog in Excel using VBA macros.

Other examples:

• How to Export Data As PDF in Excel with VBA macros
• How to Send Meeting Invites From Excel with VBA macros
• How to put Range Data Into Array with Excel VBA macros