Question on how to populate Color property of Excel objects, i.e backcolor?

[johanna0507]

Hi all, I am putting a button on a sheet and I would like to use a specific colour, not one of the default ones provided by Excel.
I can obviously get the colour definition in RGB or Hex, but Excel seems to want a format that I am not familiar with, and have been unable to find any information on.

For example, a red shade is defined like this: &H000040C0&

Where can I find information on how to get the codes that Excel accepts so I can make my objects the shade of colour that I am after....?

Hope somebody can help!

[ragulduy]

Where do you find the &H000040C0& reference?

I use excel 2003, I can assign a objects colour as RGB by going to format autoshape -> colours and lines -> selecting the colour drop down -> more colours -> custom tab.

You can then use either RGB or HSL colour models to put custom colours in.

[Norie]

The last 6 digits, 0040C0, of &H000040C0& are the RGB for the colour.

[johanna0507]

The last 6 digits, 0040C0, of &H000040C0& are the RGB for the colour.

That can't be right, can it? There are no letters in RGB.... Besides, there can be up to 3 digits for each of the Red, Green, Blue shades, so that format ought not cover it...

[ragulduy]

They are in HEX format

http://en.wikipedia.org/wiki/Web_col...to_hexadecimal

[johanna0507]

If it is Hex, then it needs some serious formatting.

The current format is "&H00008000&" for a Dark Green color. This code does not look like Hex. The Hex for any shade of green that I know, does not look like that at all. Neither does the RGB code.

But if it is somehow hex anyway, then please could you help me to explain how to convert the hex for Green into the format that Excel apparently needs, to understand what colour this is?

Steps to reproduce:
1) Put an object Button on an Excel sheet.
2) Select Properties and then check the 5th option from the top, which is backcolor. This allows you to select default colours to change the colour of the button, but there is also a code available. This is the code I am talking about, and it does NOT look like Hex or RGB to me.

[Norie]

Perhaps you could take a look at the RGB function.

[Kyle123]

It is hex, google "#008000" it's dark green

Have a look here for conversions: http://www.colorhexa.com/008000

[johanna0507]

Perhaps you could take a look at the RGB function.

I am very well familiar with RGB since I have been a java programmer for many years.

I am not looking for a function here, but a property, and I am not looking to learn VBA programming, just understand how I can change the colour of the button to the colour that I want.

Colour Red:
-------------------------
RGB colour format = RGB(255, 0, 0)
Excel object property format = &H00008000&

???

I see no correlation, but if there is one I would be really grateful for a straightforward answer, or a link to a page that definitely explains how this works. Checking the Excel RGB function, it does not indicate how to set the button property to a specific shade.

[johanna0507]

It is hex, google "#008000" it's dark green

Have a look here for conversions: http://www.colorhexa.com/008000

That looks more promising, I'll check it. Thanks!



Edit - No, this is not it either. :-(

Excel seems to want a code starting with "&" (ampersand) followed by "H", then 7 numbers, then the letter "C" and another ampersand, like this: "&H00008000&"

There is nothing on that page that comes up with the colour I'd expect based on any of the formats on the page. Trying to input the numbers/letters from RGB or hex gives an error or a completely unexpected colour.


I can get the button to randomly change colour by changing the digits in the 7 number string, but as of right now, it's completely random, I don't see any logic as to how it changes.


For example, black, like Excel's default font colour, seems to be &H80000012& (ForeColor property)


---------------------

This PDF explains how this is not actually colours but a semi-dynamic reference to a holder in Windows. http://www.johnsmiley.com/cis18/Smiley009.pdf
Meaning that what settings you got in these colour holders in the Windows colour scheme, specifies what colour you get from these codes.

Hence, me choosing one particular colour, might produce a completely different colour on an olde machine, or a machine where the user has heavily customised the colour scheme.

In other words, this property is more or less useless as it is not consistent or reliable at all.

[ragulduy]

Johanna,

I think Norie meant, in answer to your question on how to convert between the format used by excel (HEX) and RGB format, you can use the RGB funtion in VBA.

[johanna0507]

Johanna,

I think Norie meant, in answer to your question on how to convert between the format used by excel (HEX) and RGB format, you can use the RGB funtion in VBA.

Thanks - unfortunately I do not know VBA programming and do not have time to learn it.
I just wanted to turn a button on a spreadsheet into the colour of my choice. The only function of the button is to activate another tab. This is ridiculously simple stuff, but something that my boss asked me to include for this spreadsheet.

I had NO idea that setting the button colour would be this complicated, and it seems that what I want to achieve (i.e. set the colour of the button to my exact preference of colour) is not in fact possible, or not possible unless I learn VBA programming first, which I do not want to do.

[Izandol]

If you know the RGB values you wish to use, you may input them directly to the property. Click the dropdown button in the property you require, select the Palette tab and then right-click an empty colour square.

[ragulduy]

did you see what I put in post #2?

[Kyle123]

HEX colours in VB6 aren't RRGGBB, they are BBGGRR, so if I wanted #cc0000, it would become: "&H000000CC" as a string or &H000000CC& which is &HCC& as long note the ampersand

[johanna0507]

did you see what I put in post #2?

I have Excel 2010 and I checked for anything resembling what you were talking about, but could not find it. There was a MAJOR change in the GUI for Excel between 2003 which you are using, and 2010 which I am using. There is nothing called "Color Autoshape" anywhere that I can spot, in the Excel 2010 GUI.

If you know the RGB values you wish to use, you may input them directly to the property. Click the dropdown button in the property you require, select the Palette tab and then right-click an empty colour square.

Hi, Yes I can select a blank square (no colour). But no variation of inputting the RGB code seems to work. I've tried:

• 255,0,0
• RGB(255,0,0)
• 25500 (this adds the ampersand and the letters, but creates an Orange-brown shade) rather than Red as it is supposed to do

Would you be able to explain how to change the RGB so that it works?

[johanna0507]

HEX colours in VB6 aren't RRGGBB, they are BBGGRR, so if I wanted #cc0000, it would become: "&H000000CC" as a string or &H000000CC& which is &HCC& as long note the ampersand

Hi yes, thanks! I came across this when googling, but it did still not tell me the format.
I'll check, based on your suggestion.

However RGB, can take between 3 and 9 digits - the format you list seems to suggest 2 digits x 3 ?



I take as an example, a sort of Heather shade of Purple RGB(149,102,138)
So I make this 138102149

No variation of trying to enter this into the Back Color property field for the object works - get an error whatever I put.

[ragulduy]

Hi Johanna, the same option exists in 2010 I believe.

When you right click on a shape in 2010 and go to format shape, there is an option for fill in the menu on the left hand side.

You can then check the option for solid fill and when you click the drop down color palette the bottom line says "more colours"

if you click this you get the same form you get in 2003, where you can select custom and and input using either RGB or HSL colour models.

[johanna0507]

Hi Johanna, the same option exists in 2010 I believe.

When you right click on a shape in 2010 and go to format shape, there is an option for fill in the menu on the left hand side.

You can then check the option for solid fill and when you click the drop down color palette the bottom line says "more colours"

if you click this you get the same form you get in 2003, where you can select custom and and input using either RGB or HSL colour models.

Hi - no; I right click and get Format Object and Properties as the relevant choices. Neither allows to set the colour in an easy way. This was the first thing I checked for. The closest I've been able to get, is this property that I'm talking about. The commands for changing colour etc in the menu tabs are greyed out since it's an object and not a regular cell or text.

[Norie]

Johanna

Is this a Forms or ActiveX button?

[Kyle123]

No, you can't just put RGB values into a hex format, the hex code for RGB(149,102,138) is #95668a, which would be: &H8A6695

[ragulduy]

Johanna,

Apologies, I thought we were talking about shapes here (try doing what I said on a rectangle..) I wasn't aware you could change the background colour of a forms/activex control. My understanding was that you got around this by using a rectangle shape and assigning the macro to the onclick event.

[Izandol]

Hi, Yes I can select a blank square (no colour). But no variation of inputting the RGB code seems to work. I've tried:
Would you be able to explain how to change the RGB so that it works?

If you right-click a blank square you will see a dialog that allows you to choose RGB values as well as HSL. Select what you need then Add Color. If you choose an existing colour, nothing will be added, but you can simply select that colour for your button and then press OK.

[Norie]

yudlugar

I don't think you can change the back colour of a Forms button, not without code anyway and I'm not even sure code will work.

[johanna0507]

Johanna

Is this a Forms or ActiveX button?

Good question, I didn't actually know, but upon checking it turns out it's an Active X button (copied it from another sheet, and frankly didn't realise Excel catered for Active X at all.... )

I certainly don't need Active X, since all I want the button to do, is open another tab.

I'll change the button to a regular button in which case this might be easier.

Still on the Form button I cannot find the colour property there either, but it seems to look quite different, so I might have been down the complete wrong road here.

Seems the button colour is somehow tied in with the Windows colour chart and I am beginning to think I am wasting a silly amount of time on something that is purely cosmetic.

[Norie]

A Forms button doesn't have a back colour property, an ActiveX button does.

[Kyle123]

Did you read my post?

Here's a function to do what you want:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[johanna0507]

No, you can't just put RGB values into a hex format, the hex code for RGB(149,102,138) is #95668a, which would be: &H8A6695

Thanks - ok, so it was the Hex that should be reversed, not the RGB - ok. Thanks!

[johanna0507]

Did you read my post?

Here's a function to do what you want:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi - thanks for that. Sorry, for some reason I did not see the code.
thanks a lot for doing that.
I'm have never done any programming with VBA and I do not have the time to learn it now.
i don't think I can use this code because I wouldn't know where to put it.

I was under the (clearly misguided) impression that throwing in a button with some simple code in the Clicked event, to move tabs, and using different colours, would be the simplest thing in the world.

I think i was misguided here, and I believe I have been wasting all of your time, and mine.
Since I am not a VBA programmer, but a project manager, I can not spend time trying to figure out stuff like this with trial and error, or reading manuals - If I can't do it quickly, or get a quick clear response that I am able to follow, i will have to pass.

[Norie]

Try adding a rectangle, or other shape, to the sheet, then right clicking it and selecting Hyperlink...

You should then be able to create a hyperlink to an other sheet that will be followed when you click the shape.

To change the Fill colour of the shape right click and select Format...

PS When you click colour in the Fill section there's an option More colours... that will display a colour palette and the RGB for the colour you choose.

[johanna0507]

Try adding a rectangle, or other shape, to the sheet, then right clicking it and selecting Hyperlink...

You should then be able to create a hyperlink to an other sheet that will be followed when you click the shape.

To change the Fill colour of the shape right click and select Format...

PS When you click colour in the Fill section there's an option More colours... that will display a colour palette and the RGB for the colour you choose.

Good tip, thanks!!! Might try that. I think I'll have to cut my losses with this, and just to the quickest workaround I can find. What you propose sound fast.