Call Excel-Style color picker from Access (2 Viewers)

Local time
Today, 17:57
Feb 28, 2023
At first I thought this was a crazy question, but perhaps not ...

I would like to call this color picker from a button on my form:

That is from Excel, but I noticed the same color picker is available in Access if I open a form in design view and change the fore color of a text box, for example. So the dialog is available in Access, I just don't know how to call it. In Excel - - it would be Application.Dialogs(Application.Dialogs(xlDialogEditColor).Show 1, 26, 82, 48) - but there doesn't seem to be any Application.Dialogs() available in Access.

I have my code working with the standard windows Color picker, but I want to change it to use this one. The code I am using was modified from, looks like this, and does the following, which I would want the new code to do also:

When I click the circled color palette command button, it:
  • Reads the string value from the text box below it.
  • Parses the string into R,G,B elements.
  • Converts that to Long color (OLE Color).
  • Opens the color picker with that color selected.
  • Returns the newly selected color as OLE. (If the user clicks cancel, it returns the original calling color).
  • Converts that back to RGB.
  • Writes the new value to the text box below the button.
All assistance appreciated!!!
Local time
Today, 17:57
Feb 28, 2023
Don't know if this is any different, but...

No -he did the same thing I did and is essentially where I am now - i.e. he shows the default Windows form and changed the cancel function to return the called color instead of white. I did the same thing, but also had to change the input from RGB to long and the output from long to RGB.

Both of us are calling the windows color picker and I want to call the Access/Excel color picker.


Immoderate Moderator
Staff member
Local time
Today, 16:57
Feb 28, 2001
That is part of the "common dialog" library.

You might browse around in this link. I see it does contain a color reference.
Local time
Today, 17:57
Feb 28, 2023
@The_Doc_Man - Thanks, but I don't think that is the right one. I think that is for the form I have now with the 40 colors in a rectangle, not the one with the 56 colors in the hexagon.

From the page you linked:
CHOOSECOLORContains information the ChooseColor function uses to initialize the Color dialog box. After the user closes the dialog box, the system returns information about the user's selection in this structure.
That seems to align with Daniel's/my 40 color square code as:
Public Function DialogColor(Optional lDefaultColor As Variant) As String
    Dim CC                    As ChooseColor
    Dim lRetVal               As Long
    Static CustomColors(16)   As Long

    'Some predefined color, there are 16 slots available for predefined colors
    'You don't have to defined any, if you don't want to!
    CustomColors(0) = RGB(255, 255, 255)    'White
    CustomColors(1) = RGB(0, 0, 0)       'Black
    CustomColors(2) = RGB(225, 225, 225) ' Default Gray
    CustomColors(3) = RGB(255, 0, 0)     'Red
    CustomColors(4) = RGB(0, 255, 0)     'Green
    CustomColors(5) = RGB(0, 0, 255)     'Blue

    With CC
        .lStructSize = LenB(CC)
        .hwndOwner = Application.hWndAccessApp
        If IsNull(lDefaultColor) = False _
           And IsMissing(lDefaultColor) = False Then .rgbResult = lDefaultColor    'Set the initial color of the dialog
        .lpCustColors = VarPtr(CustomColors(0))
    End With
    lRetVal = ChooseColor(CC)
    If lRetVal = 0 Then
        'Exit Function
        'Cancelled by the user
        'DialogColor = RGB(255, 255, 255)    ' White -> 16777215
        'DialogColor = Me.Text0
        DialogColor = OLEtoRGB(CLng(lDefaultColor))
 '       MsgBox "Cancelled"
'        DialogColor = CC.rgbResult
        DialogColor = OLEtoRGB(CC.rgbResult)
    End If
'    MsgBox DialogColor
End Function

What I want from the should be something like: Application.Dialogs(xlDialogEditColor).Show 1, 26, 82, 48 but I don't see Application.Dialogs in Access, much less Application.Dialogs(acDialogEditColor).Show
Local time
Today, 17:57
Feb 28, 2023
Somewhat of a workaround - - That is nicely done and gives me something like the Standard Colors tab of the hexagon, but no ability to select custom colors.

I can have one button to call up that color picker and another button to call the Win API CP.

Or I could add a button on that grid CP form to open the API CP and pass the selected color to it - both are a bit clunky, but closer to the Excel style.
Local time
Today, 17:57
Feb 28, 2023
Another test - I didn't know if this would be usable, but I thought it would work - it didn't.

The Excel-Off-The-Grid code Works IN EXCEL.

I tried the following (and added a reference to the Excel 16.0 library):
Sub TestA()
     Dim xl As Object
     Set xl = CreateObject("Excel.Application")
     Set xl = Nothing
End Sub
And got:
"Run-Time Error '1004':
Show Method of Dialog Class Failed"

FWIW ...

Users who are viewing this thread

Top Bottom