VBA to set the default color (picker?)

[Ronald]

Hi,

Question.
At the top ribbon (Home, Insert, Page Layout etc.) you have the button to fill a cell colour (tab Home).
Is there any way to set this color to a wanted one?

What I want.
To have a VBA part that sets the default picked colour (as stated above) based on the current cell colour.
Retrieve the current cell colour is no issue but set the default colour on the picker (or whatever you call it) to this colour is a different issue....

Many thanks on forehand on any help you could provide!

Ronald.

 

[Luke M]

You might be able to jerry-rig something together using XML to modify the ribbon...but I wouldn't recommend it. Instead, I'd write a short macro to do what you say, and then assign it to the Quick Access Toolbar (QAT), so I can jsut hit that button instead.

Am curious though, what use would what you describe be? If the color always changes to whatever the cell's current color is, you could never accomplish anything with one click, you'd always be having to choose from the dropdown.

 

[Ronald]

Thanks for your response Luke.
Indeed, I wanted to put a button on the QAT with a macro behind it.
As I said, I manage to retrieve the colour n.r from the current cel but I can't manage to set the default colour (colour icon on the QAT) to this value.

Q:
Can anyone help me out with the code how to set this default picker colour (on the QAT) to a certain value with VAB?

Luke,
I often need to change cells colour (not in sequence) to the colour from another cell. It's a short way to do if I can click on a custom button that retrieves the colour from the current cell and set the default on the ribbon to this one. After that I only need to pick the cells that need to be change and click on the fill button on the ribbon (that was just set to the wanted colour).
This is very handy when repetitive actions are needed.

 

[Luke M]

To go the route you describe:
https://msdn.microsoft.com/en-us/magazine/cc163410.aspx

I haven't done much with XML editing yet, but it would certainly be an interesting to read if you solve this problem this way.

If it were me, I'd write a macro that copied current selected cells color to a blank cell somewhere (say, a hidden sheet), and then another macro that goes to the hidden cell, gets the color, and applies it to currently selected cells. Would do the same thing, and requires less complexity.

 

[Ronald]

Thanks Luke.
I finally did add two buttons on my QAT. One gets the color and the other applies it (number in variable).

All good now.

Thanks all for your reading and/or your reply.