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

 

[Dan_W]

Sorry. Tear off palette is the term used by MS for the floating palette.
Removed from Excel after the 2007 version.

Ahh, yes, I was going to ask if you perhaps meant to say 'deprecated', which it most definitely is. It was an 'odd' decision made by Microsoft to remove this useful functionality.

 

[zookeepertx]

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.

This looks interesting; very cool animation! Although I have absolutely NO clue what an XLAM file is, LOL! This is my work computer, so I wouldn't be allowed to download anything onto it, if that's what's required with an XLAM file.
I do use specific colors - the same ones with no room for variation. So, I need to be able to pick from the same ones each time I need to color a cell. Sometimes I need to filter by color and if there are 2 really close but not exactly the same colors, that'll pose a problem.
I just don't know what the heck TPTB at Microsoft were thinking when they got rid of the floating palette! It seems like such a simple but incredibly useful feature. Sometimes I'll need to color 1 cell what I call Screaming Green, the next cell Red, the next cell Screaming Green again them maybe 10 cells that don't need colored at all and so on. It's such a pain to have to open the little palette box every, single time. (I know, I could use right click and format painter, but I might as well use the palette if I'm going to do that.
So, enough rambling on my part - sorry about that.
Anyway, your idea about a custom ribbon for coloring cells looks like it has potential as well.

Just let me know what you might come up with. Do you have any inside contacts with Microsoft that you can tell to "clean up their act" and give us back the floating boxes?

Thanks
Jenny

 

[Dan_W]

Hi. Sorry for the delay in getting back to you. I had to move over the weekend and moving is never fun!

So an XLAM file is a specific type of XLSM file - which itself is a standard XLSX file with macros enabled. An XLAM file just functions effectively as an addin, but does not require installation, thus making it ideal for distribution. Each of the solutions I mentioned, though, do require VBA - is a VBA-powered solution a possibility?

I do use specific colors - the same ones with no room for variation. So, I need to be able to pick from the same ones each time I need to color a cell. Sometimes I need to filter by color and if there are 2 really close but not exactly the same colors, that'll pose a problem.

If so, then yes, it seems like the custom ribbon might be the easiest to use, so will take another go at trying to find a working version of it to show you. As another possibility, a more flexible potential solution would be customise the 'right click' menu, so instead of the normal context menu, you can either replace it entire with something like:

or you the colour options as a submenu to the existing context menu. None of it would be permanent, and you could make its appearance contingent on certain conditions being met (e.g., you happen to be holding down a shift key when you press right click, etc). Again, this requires that VBA be enabled in your Excel. Here is a link to the code: Right Mouse Click Menu extra functionality

The sample code at the linked page adds/deducts numbers of the value in the cell, but it can be easily adjusted to change the interior color of a selection.

I just don't know what the heck TPTB at Microsoft were thinking when they got rid of the floating palette!

Couldn't agree more. I think where it really irks me are cases where the functionality has been built into on of the Office applications, but not the others - for example, the Colour Picker. You can find it in Powerpoint, but not Excel. Why!? No idea, but then I'm just a mere mortal. Anyway, I've long since added this, along with the tear-off palette issue, to my list of "WTF MS?!" items. Oh well.

 

[Dan_W]

Here is a sample of the custom ribbon op

tion. The colors here were used because these color square icons are already built-in, meaning that as it currently stands, the colours are hardcoded in. It is entirely possible to customise the color selection, but I thought I'd first show you what it would (generally) look like, and to check whether VBA was an option before I launched into rewriting the code.

If this does look like a workable solution, roughly how many colors would you be looking to add? (there isn't really a hard limit, just curious). Also, do you know the colors by RGB value? Hex value? etc.

 

[zookeepertx]

Here is a sample of the custom ribbon op

tion. The colors here were used because these color square icons are already built-in, meaning that as it currently stands, the colours are hardcoded in. It is entirely possible to customise the color selection, but I thought I'd first show you what it would (generally) look like, and to check whether VBA was an option before I launched into rewriting the code.

If this does look like a workable solution, roughly how many colors would you be looking to add? (there isn't really a hard limit, just curious). Also, do you know the colors by RGB value? Hex value? etc.

No, I don't envy you moving; that really ISN'T any fun! I tried to move to a new apartment and move my horses to a new stable on the same weekend once. I finally gave up and talked my landlord into letting me stay another week and get things going right, LOL!

So, this looks like a great solution! It'd stay open as long as I needed it? That would be perfect! Let me go through my spreadsheets that I use a lot and get the RGB values and I'll get back to you. (They keep wanting me to do my regular work instead of improving existing spreadsheets. They're no fun at all.

)

 

[Dan_W]

Honestly, what could possibly be more important than making spreadsheets pretty?!? I can't think of anything, and I ponder the topic regularly.

Yes, it would stay open for as long as you have the ribbon open. Ideally, I would update the code to make it usable to anyone who happens to stumble across this thread, so I'll have to give some thought to how that should look (e.g. a settings windows, where you can select/save the palette preferences etc). If you have any preferences, do let me know.

 

[zookeepertx]

Honestly, what could possibly be more important than making spreadsheets pretty?!? I can't think of anything, and I ponder the topic regularly.

Yes, it would stay open for as long as you have the ribbon open. Ideally, I would update the code to make it usable to anyone who happens to stumble across this thread, so I'll have to give some thought to how that should look (e.g. a settings windows, where you can select/save the palette preferences etc). If you have any preferences, do let me know.

Right? If nothing else, making them look good makes it easier to me to get what I need from them! I'm also a ****** for color coding things, so here we are. LOL! Plus, often when I'm "prettying" them up, I find a way to make them work better, too.

 

[jolivanes]

Just a question, because I don't know, for Dan_W
Would it be easier with a Userform with colored labels or TextBoxes or whatever that are colored with the needed different colors?

 

[Dan_W]

Just a question, because I don't know, for Dan_W
Would it be easier with a Userform with colored labels or TextBoxes or whatever that are colored with the needed different colors?

Which is essentially what my first suggestion was: Excel color palette

It is, afterall, just a modeless userform with labels the user clicks to select a colour. From my perspective, it's all much of a muchness because I've coded all the above solutions already, it's just a question of adapting to someone else's needs, I think, and making sure that the code isn't woefully embarrassing

Of course, the benefit of using a userform is that you could move the form around as required, rather than having to move the cursor back and forth between the ribbon and the worksheet... .my primary concern thus far is just making sure that VBA is a workable solution, and isn't blocked by the work computers.

The best solution would be if MS could reverse course on this decision of theirs to make the palette modal... because...why did they have to do that?!?! Sigh

 

[jolivanes]

Hi Dan.
This problem is not the only one Microsoft has created from what I gather reading some of the posts in excel forums.

I quickly did a test and it looks like this might do what you want.

In a regular module

Sub Show_Me()
    Color_Palette.Show vbModeless
End Sub

Insert a UserForm and in it's Properties Window change it's name from "UserForm1" to "Color_Palette"
Add as many Labels as required for all the different colors you want.
In the Properties Window for each label at the "Backcolor" line, select "palette" and click on the color that you want that Label to have.
Do this for all your Labels.
For each Label, use this code, changing the "Label1_Click" to "Label2_Click", "Label3_Click" and so on.

Private Sub Label1_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label1").BackColor    '<---- Change Label number as required
End Sub

When back in your sheet, call the "Color_Palette", select your range, single cell or multiple cells, to be colored and click on the label that has the desired color.

If I knew how to get the name of the clicked Label, code could be made less cumbersome.