0 votes
in VBA by Beginner (9 points)

I want to add my own `Color Pallete` in Integrated “DropDownColorPicker” is it possible?

Here is the discussion or i want to know how to integrate this “DropDownColorPicker” in `Office`?


3 Answers

+1 vote
by Skilled (749 points)
edited by

Here is code for ChooseColor_Dialog that enables you to pre-define your own color pallette:

' These Windows API declarations require VBA7 (Office 2010 or later)
Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
Declare PtrSafe Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" _
    (lpCC As CC_Type) As Long
Const CC_RGBINIT As Long = &H1
Const CC_FULLOPEN As Long = &H2
Const CC_SHOWHELP As Long = &H8
Const CC_ENABLEHOOK As Long = &H10
Const CC_SOLIDCOLOR As Long = &H80
Const CC_ANYCOLOR As Long = &H100
Type CC_Type ' see http://docs.microsoft.com/en-us/windows/win32/api/commdlg/ns-commdlg-choosecolora~r1
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    rgbResult As Long
    lpCustColors As Long
    flags As Long
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Function ChooseColor_Dialog(DefaultColor As Long, CustomColors() As Long) As Long
' Display ChooseColor API with custom colors; return chosen color RGB or -1 if none.
' DefaultColor and CustomColors(0 To 15) must be RGB from &H000000 to &HFFFFFF.
'   see http://www.cpearson.com/excel/colors.aspx
    Dim lpCC As CC_Type
    If LBound(CustomColors) <> 0 Or UBound(CustomColors) <> 15 Then
        Err.Raise xlErrRef, , "Dim CustomColors(0 To 15) for ChooseColor_Dialog"
        Exit Function
    End If
    With lpCC
        .lStructSize = LenB(lpCC)
        .hwndOwner = GetActiveWindow()
        .rgbResult = DefaultColor
        .lpCustColors = VarPtr(CustomColors(0))
    End With
    If ChooseColor(lpCC) Then
        ChooseColor_Dialog = lpCC.rgbResult
        ChooseColor_Dialog = -1
    End If
End Function

Example of use:

Static bInit As Boolean                                 ' False before initialized; True after
Static wColor As Long, wCustomColors(0 To 15) As Long   ' changes will persist during current instance
Const wDefault As Long = &HE1FFFF                       ' default color

If Not bInit Then                                       ' initialize
    myCustomColors = Array( _
        wDefault, &HE1E1FF, &HE1FFE1, &HFFFFE1, &HFFE1FF, &H80FFE1, &HFFE1E1, &HE1E1E1, _
        &H80FFFF, &HE180FF, &H80E1E1, &HE1FF80, &HFF80E1, &H80E1FF, &HFFE180, &HFFFFFF)
    wColor = -1
    For n = 0 To UBound(myCustomColors)
        wCustomColors(n) = myCustomColors(n)
    Next n
    bInit = True
End If
' the following statements might be repeated several times during the current application instance
' changes to wColor and wCustomColors will persist
If wColor < 0 Then wColor = wDefault
wColor = ChooseColor_Dialog(wColor, wCustomColors)
If wColor < 0 Then
    ' user clicked Cancel
End If

The resulting dialog looks like this:

Clicking Define Custom Colors expands the dialog allowing changes to the highlighted custom color item from array wCustomColors.

by Skilled (749 points)

I corrected an error in the ChooseColor_Dialog function.
Before: .lStructSize = Len(lpCC)
After: .lStructSize = LenB(lpCC)

The previous version failed in the 64-bit version of Microsoft Office. See "A common pitfall - The size of user-defined types."

0 votes
by Skilled (749 points)
edited by

I believe this was answered in the Stack Overflow article you initiated. Also, see this related question you asked here. Also, see this tutorial: How to Make Custom Ribbons in Excel VBA.

by Beginner (9 points)
On StackOverflow I answered my own question.
0 votes
by Skilled (749 points)

Have you considered this tutorial: Show Color Palette Dialog Box with VBA

by Beginner (9 points)


...yes it was interesting but not what I was looking for.

by Beginner (9 points)


My friend, I've read your first block of code, but I haven't figured it out since I'm not strong at this kind of thing. 

Also, I googled the flags and some words to understood what that means!? It's still not very clear yet.

 I've read these docs.ms, cperson.

 In your code I see `Function` but how can I use it? If I - Excel > Sheet > Cell > then type =ChooseColor_Dialog > ENTER ••• it's not working sad


I've read this link too Show Color Palette Dialog Box.

<~~ <~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~~ ~> ~~>

2nd block of code looks more interesting but I don't know how to make it to the working look. Don't know idea how to run it?

 I think you know how to do this. I need a button after clicking on which the `Color Palette` will appear.  enlightened

This is interesting:

 heart  Best Regards! 

by Skilled (749 points)


Sorry. When you initiated a question on this VBA Q&A web site I assumed you knew something about VBA. Perhaps you should start at Chapter 1 of the Excel VBA Tutorials web site. Good luck.

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.


For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.