Open Colour Select Dialog Box

[High Plains Grifter]

Hi, People

I am making a macro which uses useforms to guide users through a selection process; one step requires the user to be able to define a colour. To make this friendly and pleasant, I would like to invoke the colour selection dialog box that can be seen by clicking on the fill colour icon in the excel ribbon and then selecting "More Colours". The dialog contains two tabs, one entitled "Standard" and the other "Custom".

My question is this - Can I access this dialog in my macro, and if so, how would I do so to return a colour which can be used later in the macro? I don't necessarily need both tabs; either will do for my porpoises (although I prefer "custom" for it's greater flexibility).

Thanks for your time!

 

[tlowry]

Take a look at:

http://erlandsendata.no/?p=3227

which had the following code:

Function GetUserSelectedColor(Optional lngInitialColor As Long = 16777215) As Long
Dim lngResult As Long, lngO As Long, intR As Integer, intG As Integer, intB As Integer
    lngResult = xlNone ' default function result
    ' this function requires that a workbook is active
    If Not ActiveWorkbook Is Nothing Then
        ' save the original first palette color so it is possible to restore it
        lngO = ActiveWorkbook.Colors(1)
        ' get the RGB values of lngInitialColor
        intR = lngInitialColor And 255
        intG = lngInitialColor \ 256 And 255
        intB = lngInitialColor \ 256 ^ 2 And 255
        If Application.Dialogs(xlDialogEditColor).show(1, intR, intG, intB) = True Then
            ' the user selected a color and the first color in the palette was updated
            lngResult = ActiveWorkbook.Colors(1)
            ' reset the changed palette color to the original color
            ActiveWorkbook.Colors(1) = lngO
        End If
    End If
    GetUserSelectedColor = lngResult
End Function

 

[High Plains Grifter]

Thanks, tlowry - this looks exactly what I was after! I'll try it out over the next few days but I have no doubt that it will give me exactly what I want. The examples on the link you gave me are good as well.

Perfect!