Excel color palette

[zookeepertx]

Hello all,
It used to be that you could right-click to show the Cell Color palette to change a cell's color and then grab the palette and drag the whole box down so it showed all the time. You could do the same thing with the Font Color palette. Now, you have to right-click and select directly from the displayed palette instead of having the palette already there to just choose from.
I have several worksheets that I have to go through and evaluate a large number of cells, one at a time, then decide what color to make the cell. Because there are so many cells to evaluate, it's kind of a (small) pain to have to ask for the palette every single time I need to change a cell's color. Is there a way to get that little box with the color palette to show all the time like you used to be able to do?
Conditional Formatting won't work because there is such a variance of reasons why a cell needs to be a particular color.

This little box is what I'm talking about.

(I hope this question is clear. I tend to "over-describe" things and confuse people when I'm typing/writing)

Thanks!

Jenny

 

[zookeepertx]

vbModeless will keep it open until you close it.
I made one to try it with only 10 colors and it works like a charm.
As a matter of fact, I added it to the QAT Userform

I DID IT!! I figured it out!! I made a userform (following your suggestion in a previous post) that I can call up with a macro, and it STAYS OPEN as long as I need it to! I'm doing a happy dance over here!

Nothing I try to do ever works without someone fixing it for me. WOOHOO!

I did have a little trouble with the Hex code when coloring the labels in the userform because of the characters being in a different order, but once Google helped me figure that out, it all came together.
I used your code to make the macro to call up the Userform:

Sub Show_Me()
    Color_Palette.Show vbModeless
End Sub

And then created all the colors of the labels. The coding for putting those colors into the cells is a bit long though. Is there any way to clean it up? And I assume there's no way to incorporate the Userform's coding in the macro module, right? Just in case someone else wants to use this, I'd hate if I gave them the macro module and forgot to give them the Userform module.

Private Sub Label1_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label1").BackColor
End Sub
Private Sub Label2_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label2").BackColor
End Sub
Private Sub Label3_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label3").BackColor
End Sub
Private Sub Label4_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label4").BackColor
End Sub
Private Sub Label5_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label5").BackColor
End Sub
Private Sub Label6_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label6").BackColor
End Sub
Private Sub Label7_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label7").BackColor
End Sub
Private Sub Label8_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label8").BackColor
End Sub

(I didn't include the code for ALL the labels; it seemed redundant, LOL!)

Also, I created it all in a dummy workbook that I used to play with but want to have it all available in my Personal workbook. Do I have to do anything special to do that or can I just move the macro and the Userform into the Personal workbook in the project window. And do I have to change any of the coding? I only ask because I don't really get what Private Sub means. I googled it and only got more confused.

 

[Yeft]

Hello Jenny,

Try Pressing down Alt Key and simultaneously clicking on the H key twice ,then once on the M key;

With the cell selected, press Alt+H+H+M keys. Then the Little palette of colors will pop up.

 

[zookeepertx]

Hello Jenny,

Try Pressing down Alt Key and simultaneously clicking on the H key twice ,then once on the M key;

With the cell selected, press Alt+H+H+M keys. Then the Little palette of colors will pop up.

Thanks for the suggestion, but the box still doesn't stay open after I select the color for that cell/cells. Before Microsoft "upgraded" stuff (which seems to almost never be a good thing, IMHO) you could open the little square palette box, drag it somewhere and it would just stay there. You could select a cell, choose a color, select another cell, choose a different color, etc. without the palette box disappearing. That way you didn't have to open the palette every time you wanted to color a cell.

 

[daverunt]

Tear off palette redundant in 2007 onward.

 

[zookeepertx]

Tear off palette redundant in 2007 onward.

Thanks for the response, but I don't get what you're saying. I mean, I know what "redundant" means, but I don't know how it applies here.

I assume when you refer to "tear off palette" you mean the "floating" palette that I used to be able to move down into the working area so I could choose a color without having to call up the palette. But I need to know how to tear it off/make it float like I used to. I used to be able to do the font color palette, too.

It's just that, on various worksheets, I have to change cell colors and font colors so many times, it's just annoying to have to keep calling up the palette every time.

Any ideas would be greatly appreciated!

Jenny

 

[zookeepertx]

Well, after googling some more, it appears that this isn't possible. Other people are complaining about the same thing. (I've fussed about it for a long time; just never got around to posting, hoping for a solution till now).

Here's the article I found:

Redirecting

I'm bummed!

** IF ANYONE KNOWS DIFFERENT, I'D LOVE TO HEAR ABOUT IT!! **

Jenny

 

[Dan_W]

Since first visiting this forum years ago, I have long since learnt that very, very few things are impossible.
I have also experienced this frustration. My 'solution' is a bit half-baked, but give me five minutes to dig it out.

 

[Dan_W]

Ok - apologies for the delay - the laptop I'm using at the moment is staggeringly slow. I've started work on two solutions - one is simply to make a whole new color palette as an add-in (i.e., an XLAM file that you load an it would appear as a button in the ribbon that would sit on the worksheet. and would allow you to change the colour of selected cells.

 

[Dan_W]

Sorry for the disjointed reply and the delay - I've been fighting a dozen wars with technology over the past hour.
So the above colour picker is a Work In Progress - there is an eyedropper and it works with palettes too, but there are a lot of little things I need to do to it to get it into some kind of working condition.

A little more developed, though a bit less flexible is a custom ribbon. This is something I made years ago, and essentially it fills a custom tab with two dozen buttons, each with a designated colour. This is probably more useful when you know thatyou have a certain colours that you need to keep selecting between. What colours are assigned to what button is entirely customizable too. I tried to dig out a file that demonstrates it, but it's on another computer, and I was conscious that my "give me five mins" comment was starting to look farcical.