Call Excel-Style color picker from Access

[Marshall Brooks]

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 - https://exceloffthegrid.com/calling-and-using-the-color-picker-with-vba/ - 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 https://www.devhut.net/vba-choosecolor-api-x32-x64/, 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!!!

 

[The_Doc_Man]

Don't know if this is any different, but...

Open the Windows Color Dialog from VBA

If you need to get Access color values from your users, this is the easiest way to do it.

nolongerset.com

 

[Marshall Brooks]

Don't know if this is any different, but...

Open the Windows Color Dialog from VBA

If you need to get Access color values from your users, this is the easiest way to do it.

nolongerset.com

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.

 

[The_Doc_Man]

That is part of the "common dialog" library.

Common Dialog Box Library - Win32 apps

This section discusses the Common Dialog Box Library. The Common Dialog Box Library contains a set of dialog boxes for performing common tasks, such as opening files and printing documents.

learn.microsoft.com

You might browse around in this link. I see it does contain a color reference.

 

[Marshall Brooks]

@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:

CHOOSECOLOR

Contains 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
        .flags = CC_ANYCOLOR Or CC_FULLOPEN Or CC_PREVENTFULLOPEN Or CC_RGBINIT
        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"
    Else
'        DialogColor = CC.rgbResult
        DialogColor = OLEtoRGB(CC.rgbResult)
    End If
'    MsgBox DialogColor
End Function

What I want from the https://exceloffthegrid.com/calling-and-using-the-color-picker-with-vba/ 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

 

[Marshall Brooks]

Somewhat of a workaround - https://www.access-programmers.co.uk/forums/threads/color-picker.271714/ - 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.

 

[Marshall Brooks]

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")
      xl.Application.Dialogs(xlDialogEditColor).Show
     Set xl = Nothing
End Sub

And got:
"Run-Time Error '1004':
Show Method of Dialog Class Failed"

FWIW ...