• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match colour of Macro Button (Command Button)

Jack

Jack

Member
Does anyone know the RGB colour code for the stadard macro button (ie CommandButton) by any chance?

I am trying to match a cell to this colour but after a lot of searching have struck out. I just wanted to use in this simple little macro.


Sub FillColourtoMacroButton()

ActiveCell.Interior.Color = RGB(?, ?, ?)

End Sub


Thanks for any suggestions.
 
Hi John ,


Can you try 206 , 206 and 202 , and see if it is the same ? Even if it is not the same , some trial and error around these values should do the job , I think.


Narayan
 
Hi Narayan


Thanks but its a fair bit different - gives quite a grey colour. The trial and error for this is really tedious. I am just hoping someone out there has done exactly this. I might set up a sheet with slider control in for each colour and find the match that way. I suppose it will be a good exercise for me and handy for finding any colour wanted.


Thanks again for your time.
 
Hi John ,


I just typed in this question in google , and one of the result pages had this piece of software :


http://www.nattyware.com/pixie.php


This shows you the RGB codes of any pixel over which you take your mouse cursor ! Using this on the Excel command button gives 212 , 208 , 200.


Why don't you try this and see if it gives you what you are looking for ?


Narayan
 
Hi again Narayan ok will check it out and let you know how I get on.


Have a great weekend!
 
Narayan I just find 10 mins to try this, it's a great little tool so thanks for the tip. It says it's 236,233,216) but when put into the macro was way off, like a yellow.


However if I make it a custom colour for 2003 it's tools/options/color) it works perfectly!


So the VBA code I have must be wrong. So I have a solution at least for this workbook thanks to you. I would like to still understand why the VBA code doesn't give the same result as using custom colors. I will record a macro and see if I can figure it out.

Cheers
 
Since the default color of standard controls are based on system colors, they can vary according to version of windows and the user's theme/color settings in Windows. Windows has special color values that instead of being true RGB values are linked to the color theme properties. VBA has constants for them.

Look up "SystemColorConstants" in the object browser or "System Color Constants" in Help, and use those constants in place of RGB() to get the matching color that will follow the Windows theme.


Asa
 
asa you certainly pointed me in the right direction with the SystemColorContants class so thank you again.


I eventually through trial and error and in particular using a UDF code from other places made this work. I really don't understand why it works but it does and I only got there through lots of trial and error. Perhaps others in say like Vijay can explain it.


Apologies to anyone out these on the web who came up with the UDF - I went to so many places I lost track of where I got this.


Step 1 go into the Object Browser and under AllLibraries enter SystemColorContants and you will see the various members you could use - I was interested in ButtonFace. Click on the one you want and below in the last area of the Object Browser you will see some numbers pertaining to this object ConstvbButtonFace = -2147483633 (&H8000000F)


Step 2 enter this code into the top of a module

Private Declare Function OleTranslateColor Lib "oleaut32" (ByVal clr As Long, _

ByVal hPal As Long, _

ByRef TranslateColor As Long) As Long


Step 3 enter these subs below above


Sub FillColourtoMacroButton()

'Needs TranslateColor Function below plus OleTranslateColor above declared as Private at top of page

'to convert number of SystemColorConstant to a number VBA can use

'Go to Object Browser and under AllLibraries enter SystemColorConstants and you get

'definition for the object you want in the case of vbButtonFace is

'-2147483633(&H8000000F)

'enter this -2147483633 number or which ever number relates to the SystemColorConstant

'you want into the TranslateColor Function and it converts to a new number - use

'this 'number in below command


ActiveCell.Interior.Color = 14215660


End Sub


Public Function TranslateColor(ByVal pColour As OLE_COLOR) As Long

'Decides if the colour passed in a a vbSystem colour

'ie. vbButtonFace and if so converts it to the long value

If OleTranslateColor(pColour, 0, TranslateColor) Then

TranslateColor = 0

End If

End Function
 
Hi John ,


Thanks for the code. The following link explains it well , I think.


http://www.vbaccelerator.com/home/vb/tips/Get_an_RGB_Color_from_an_OLE_COLOR/article.asp


Narayan
 
Great! I actually was under the impression that you could simply use
Code: 
ActiveCell.Interior.Color=vbButtonFace


Obviously not! 


Thanks too for the lesson and the code.


Your code comments say the functions need to be declared as private, but not necessarily.  If Private, they can only be called from within that module (or that class if located other than in a module).  If you want your whole project to be able to use them, you can declare them just as you have actually declared them :) (TranslateColor as Public and OleTranslateColor as Private---since it will only be called directly by TranslateColor)


Now, the translated color I assume is a true RGB color and is no longer linked to the windows theme/settings.  You might want to have your code look up the translation each time... this might also improve readability:

ActiveCell.Interior.Color = TranslateColor(vbButtonFace)


Should work.


Asa
 
Thanks asa, yes of course your statment that's much simpler, I didn't connect that. Too relieved on solving this thing - I have been like a dog with a bone on this thing and just wanted closure. Yes I think it's an every time thing for sure.


By the way if you want to colour a shape to the macro button then its just a matter of entering 236,233,216 in the shape custom color settings, no VBA. To me that's wierd because for colouring a cell ActiveCell.Interior.Color = RGB(236, 233, 216)

gives the wrong colour completely.


Thanks also Narayan, yes I saw that one but I still don't get it yet, colour is a field in itself in Excel and VBA.
 
You must log in or register to reply here.
Back
Top