Excel "Colors" dialog in VBA

[oCoCarbon]

I'm trying to call up the "Colors" dialog box that shows up when you click on Fill > "More colors". The shortcut is Alt-H-H-M in 2010 and 2007.

Is there a way to call up this specific dialog box from VBA?

 

[Ron Coderre]

I'm guessing: no
I haven't been able to display it without going through the color palette first:

Application.Dialogs(xlDialogColorPalette).Show

Searching online only resulted in posts with instructions on how to build your own color picker. Wish I could be more help.

 

[shg]

Application.Dialogs(xlDialogPatterns).Show

will get you one step away ...

 

[oCoCarbon]

@shg: My code was already using the xlDialogPatterns but I wanted to avoid the user choosing a pattern as it made the graph fill show up as grey.

I've just found this post at VBA Corner which does just what I need with a few tweaks.

'Picks new color
Function PickNewColor(Optional i_OldColor As Double = xlNone) As Double
Const BGColor As Long = 13160660  'background color of dialogue
Const ColorIndexLast As Long = 32 'index of last custom color in palette

Dim myOrgColor As Double          'original color of color index 32
Dim myNewColor As Double          'color that was picked in the dialogue
Dim myRGB_R As Integer            'RGB values of the color that will be
Dim myRGB_G As Integer            'displayed in the dialogue as
Dim myRGB_B As Integer            '"Current" color
  
  'save original palette color, because we don't really want to change it
  myOrgColor = ActiveWorkbook.Colors(ColorIndexLast)
  
  If i_OldColor = xlNone Then
    'get RGB values of background color, so the "Current" color looks empty
    Color2RGB BGColor, myRGB_R, myRGB_G, myRGB_B
  Else
    'get RGB values of i_OldColor
    Color2RGB i_OldColor, myRGB_R, myRGB_G, myRGB_B
  End If
  
  'call the color picker dialogue
  If Application.Dialogs(xlDialogEditColor).Show(ColorIndexLast, _
     myRGB_R, myRGB_G, myRGB_B) = True Then
    '"OK" was pressed, so Excel automatically changed the palette
    'read the new color from the palette
    PickNewColor = ActiveWorkbook.Colors(ColorIndexLast)
    'reset palette color to its original value
    ActiveWorkbook.Colors(ColorIndexLast) = myOrgColor
  Else
    '"Cancel" was pressed, palette wasn't changed
    'return old color (or xlNone if no color was passed to the function)
    PickNewColor = i_OldColor
  End If
End Function

'Converts a color to RGB values
Sub Color2RGB(ByVal i_Color As Long, _
              o_R As Integer, o_G As Integer, o_B As Integer)
  o_R = i_Color Mod 256
  i_Color = i_Color \ 256
  o_G = i_Color Mod 256
  i_Color = i_Color \ 256
  o_B = i_Color Mod 256
End Sub

@Ron: Hopefully this will help if you're ever looking to do this again in the future.

 

[oCoCarbon]

This Color2RGB function is also the first time I've seen the integer division operator. Snazzy!

 

[HarryS]

Any code to position the xlDialogEditColor to near a specified cell..??? Please

 

[Keebellah]

Any code to position the xlDialogEditColor to near a specified cell..??? Please

I tried it out adding a on Right click command and invoke the module and it opens next to the cell.
You have to make some small changes and change it to a funtion but it works nicely

 

[donfaison]

I tried out the function, but apparently it was not designed for Excel 2010 in mind, as the color I choose does not end up the one being displayed, can anyone help with that?