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 Expert (805 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_PREVENTFULLOPEN As Long = &H4
Const CC_SHOWHELP As Long = &H8
Const CC_ENABLEHOOK As Long = &H10
Const CC_ENABLETEMPLATE As Long = &H20
Const CC_ENABLETEMPLATEHANDLE As Long = &H40
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))
        .flags = CC_ANYCOLOR Or CC_SOLIDCOLOR Or CC_RGBINIT 'Or CC_FULLOPEN
    End With
    If ChooseColor(lpCC) Then
        ChooseColor_Dialog = lpCC.rgbResult
    Else
        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 Expert (805 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 Expert (805 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)
+1
On StackOverflow I answered my own question.
0 votes
by Expert (805 points)

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

by Beginner (9 points)

considered

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

by Beginner (9 points)

@JWoolley

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 Expert (805 points)
+1

@rediffusion

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
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

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

...