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.

 

[jproffer]

Maybe something like:

 If workbooks.Count > 0 Then

before your code

 

[MagiCat]

Thanks for the reply, it at least lets me give a proper error message to users if they try to change colors without having an open workbook. Since the program actually creates a workbook, I was almost thinking of just creating it early, but then it would be messy if the user cancels. Does anyone have a good way to create a new workbook silently and then delete it silently if it's not used?

 

[jproffer]

As far as "silently", you can use:

 Application.ScreenUpdating = False
 ' code here
Application.ScreenUpdating = True

As far as the syntax for creating and deleting (if cancelled) a workbook, I'd have to wait until work tomorrow to figure that out. (no excel at home)

 

[jproffer]

Ok, couldn't really test it under your conditions, so it may not be quite this straight-forward, but the syntax for adding a workbook and then closing it without saving (which is essentially what you're doing) is:

Workbooks.Add
' etc, etc, etc....
If Cancel = True Then Activewindow.Close

This is assuming that no changes are made to the new workbook and no other workbooks are activated/selected during the rest of your code.

 

[mikerickson]

Any of the built in dialogs will error if invoked when the environment is not right. For example, xlDialogCellProtection will error if Selection is a shape and not a range.

In old excel, there was a limitation of 56 colors (the .ColorIndex properties). The xlDialogEditColor edits the color that a particular ColorIndex represents.
For example, ColorIndex = 3 is red by default, but with the color edit dialog you can make is so 3 is blue, or any other color.

These custom color indexes are stored at the workbook level. Different workbooks have different customizations.

The reason the xlDialogEditColor errors when there isn't a workbook open is that excel doesn't know which workbook to apply the changes to. The environment is wrong for that dialog.

You could use this function to invoke the dialog and return the OLE_Color that the user selected.

Function GetColorFromDialog() As OLE_COLOR
    Dim tempBook As Workbook
    
    Set tempBook = Application.Workbooks.Add
    
    Application.Dialogs(xlDialogEditColor).Show 1
    Range("a1").Interior.ColorIndex = 1

    GetColorFromDialog = Range("A1").Interior.Color
    tempBook.Close savechanges:=False
End Function

 

[MagiCat]

I guess the question I have is whether there is a dialog available for selecting colors that doesn't require a workbook available? Even if not native to Excel, is there a control that can be added?

I always suggest to people to open Excel without a worksheet to save a tiny bit of time because they're always going in there to either to run a program that creates a worksheet or to edit an existing worksheet. Very rarely to people make a new worksheet and even if they do, it's from a template or just a single button press on the toolbar.

If not, my solution will probably be to have the program create the new file upon opening and if they cancel out of the program, to close the file that was created.

 

[mikerickson]

The built in dialogs mirror those used by the user from native excel. AFAIK, whenever one used a color dialog from excel, one is coloring something. A cell, a color index number, a shape, a userform control,... something. If that thing doesn't exist, the environment is wrong and the dialog won't show.

So, I guess the answer to my question is No. But, if you look at the UDF that I posted, creating and then deleting a workbook doesn't slow things down much at all.

 

[Jaafar Tribak]

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

 

[mikerickson]

Where would this code reside? It would have to be in a VB project and, therefore, a workbook. If want to make the Personal Macro Workbook visible/invisible rather than creating and destroying a new workbook, that would also work. (Also allow for the color chosen in one session to be saved for the next).

If you like the API route, but have a Mac, there is an equivalent via Apple Script.