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

Answer 1

   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