Using color picker with no open workbook

[MagiCat]

I'm using Excel 2013 and the line of code I'm having trouble with is:

If Application.Dialogs(xlDialogEditColor).Show(10, 0, 125, 125) = True Then

The code is in an Addin and it works great if there's a regular workbook open but if there isn't one, I get the error:

Run-time error '1004':

Unable to get the Show property of the Dialog class.

I find that I get this same error if I use Application.Dialogs in some cases but don't get it in other cases like using the Open File dialog.

Anyone know why this is happening or if they have an idea for a workaround?

The reason I don't have a file open is because the Add-in loads a form and the color dialog is there to change the colors of the form.

 

[MagiCat]

Have you considered using the CHOOSECOLOR API function which doesn't require an active workbook ?

Didn't even know that was a possibility. Well, I ran with that, searched Google and found code and and it seemed to work great even without a workbook loaded. I altered the code that I found at https://stackoverflow.com/questions/5724396/excel-64-bit-and-comdlg32-dll-custom-colours

In my Add In, I have the following code in a separate module called ChooseColor:

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

 Private Declare PtrSafe Function ChooseColorAPI Lib "comdlg32.dll" Alias _
 "ChooseColorA" (pChoosecolor As ChooseColor) As Long

 Dim CustomColors() As Byte

Function GetRGBColor() As Long

    Dim cc As ChooseColor
    Dim Custcolor(16) As Long
    Dim lReturn As Long
    cc.lStructSize = Len(cc)
    cc.hwndOwner = Application.Hwnd
    cc.hInstance = 0
    cc.lpCustColors = StrConv(CustomColors, vbUnicode)
    cc.flags = 0
    lReturn = ChooseColorAPI(cc)
    If lReturn <> 0 Then
        'UserForm1.Caption = "RGB Value User Chose: " & Str$(cc.rgbResult)
        'UserForm1.BackColor = cc.rgbResult            ' Visual Basic only ****
        'Application.Section(0).BackColor = cc.rgbResult ' Access only **********
        GetRGBColor = cc.rgbResult
        CustomColors = StrConv(cc.lpCustColors, vbFromUnicode)
        'For x = 0 To 16
            'Debug.Print x & "-" & CustomColors(x)
        'Next
    Else
        GetRGBColor = -1
    End If

End Function

Sub ColorSettings()
   ReDim CustomColors(0 To 16 * 4 - 1) As Byte
   Dim i As Integer

   For i = LBound(CustomColors) To UBound(CustomColors)
       CustomColors(i) = 0
   Next i
End Sub

and the following code is the click event of my command button:

    Dim thisdate As Date

    thisdate = Now
    fromDate.Value = DateSerial(Year(thisdate), Month(thisdate) - 1, 1)
    toDate.Value = DateSerial(Year(thisdate), Month(thisdate), 1) - 1

Finally, the following code is in my main body of code (separate from the form):

    With frmReports
        .BackColor = lngFormColor
        .frmOptions.BackColor = lngFormColor
        .frmChangeColors.BackColor = lngFormColor
    End With