Cannot get the Color Picker to work in Excel 2010 64 Bit

Question

Hello I am struggling to get VBA code updated for Excel 2010 64 Bit. I have checked all over, including an informative post here on StackOverflow: StackOverflow Question I do understand i have to Declare PtrSafe and create LongPtr and LongLong where applicable, but i get a "Compile Error. Type Mismatch" on the ".rgbResult" portion of the Private Function Code. Any any and all help would be greatly appreciated. My code is as follows:

Option Explicit
#If VBA7 Then
     #If Win64 Then
        Private Declare PtrSafe Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" (pChoosecolor As ChooseColor) As LongPtr
        Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
        Private Type ChooseColor
            lStructSize As LongPtr
            hwndOwner As LongPtr
            hInstance As LongPtr
            rgbResult As LongPtr
            lpCustColors As String
            flags As LongPtr
            lCustData As LongPtr
            lpfnHook As LongPtr
            lpTemplateName As String
            End Type
      #Else
       '{{{This Section of Code works ok so i have excluded it to save space as its the same as above without the ptr}}}}}
      #End If
#Else
        '{{{This Section of Code works ok so i have excluded it to save space}}}}}
#End If


#If VBA7 Then
     #If Win64 Then
            Private Declare PtrSafe Function ShowColor Lib "comdlg32.dll" Alias "ShowColorA" (pShowColor As ShowColor) As LongPtr
                Dim ChooseColorStructure As ChooseColor
                Dim Custcolor(16) As LongPtr
                Dim lReturn As LongPtr

                                        On Error GoTo ErrEnd:
                ChooseColorStructure.lStructSize = LenB(ChooseColorStructure)
                ChooseColorStructure.hwndOwner = FindWindow("XLMAIN", Application.Caption)
                ChooseColorStructure.hInstance = 0
                ChooseColorStructure.lpCustColors = StrConv(Custcolor(16), vbUnicode)
                ChooseColorStructure.flags = 0
                If ChooseColor(ChooseColorStructure) <> 0 Then
                    ShowColor = ChooseColorStructure.rgbResult

                    Custcolor(16) = StrConv(ChooseColorStructure.lpCustColors, vbFromUnicode)
                    On Error GoTo 0
                Else
                    ShowColor = -1
                End If
ErrEnd:
         End Function
    #Else
           '{{{This Section of Code works ok so i have excluded it to save space}}}}}
         #End If
#Else
             '{{{This Section of Code works ok so i have excluded it to save space}}}}}

            End Function

dunno if this would do much or not but the findwindow function im pretty sure needs to be declared with As LongPtr and you judt have it with as Long — user1759942
Also, in the posted question, the choose color function was: ChooseColorAPI instead of yours: ChooseColor — user1759942
Ahh i see that. OK i will make that update. IF you, or anyone, see anything else, I really appreciate that. I have been using a customers machine to test this so i cannot check it right now. Thanksssssss — ExcelForFreelancers
Yeah on that other post, they were different. I tried to take as much from that one as possible, however my code was still quite different. — ExcelForFreelancers

Hans Passant Hans Passant · Accepted Answer · 2014-03-14T13:28:51

   lStructSize As LongPtr

You are going overboard declaring the members LongPtr instead of Long. LongPtr should only be used if the member is a pointer or handle type, lStructSize is not a pointer. Same for several other members, including rgbResult. It needs to look like this:

    Private Type ChooseColor
        lStructSize As Long
        hwndOwner As LongPtr
        hInstance As LongPtr
        rgbResult As Long
        lpCustColors As LongPtr
        flags As Long
        lCustData As LongPtr
        lpfnHook As LongPtr
        lpTemplateName As String
    End Type

Cannot get the Color Picker to work in Excel 2010 64 Bit

2 Answers

edit