• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

Excel 2007 - Using VBA to color an object - trouble with color indexes

Have several drawing objects that need to be colored in Excel 2007 - recording a macro and setting a color to an object doesn't seem to render VB syntax...how can I tell the workbook to change to a certain color, and how can I know what color index I need to use?
0
metallironi
Asked:
metallironi
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
What drawing objects are you refering to.

With buttons you can change the backcolor of the button in the properties of the control or in VBA using something like

commmandbutton1.BackColor = RGB(0,0,0)

This article has some further information on setting the back color
http://msdn.microsoft.com/en-us/library/aa223931%28v=office.11%29.aspx

Michael
0
 
metallironiAuthor Commented:
It could be any drawing object, but in my case it's a group of rounded rectangles that I'm using to look like tabs at the top of a graphing area that will call (with visible = true syntax) a given graph associated with that shape and hide all others before turning screen updating back on...so it's not a command button - but you might be onto something - can I use that syntax with the RGB criteria to define the color of an object?  And what would the syntax be, both to call the color (is it BackColor or something else like InteriorColor) and to select the object?  Thanks for the quick reply!
0
 
Michael FowlerSolutions ConsultantCommented:
With an example I would guess trying

objectname.Backcolor = RGB(0,0,0)

If this does not work could you post an example file for me to look at

Michael
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Rory ArchibaldCommented:
For example:
    With ActiveSheet.Shapes("Rounded Rectangle 1").Fill
      .Visible = msoTrue
      .ForeColor.RGB = RGB(255, 0, 0)
      .Transparency = 0
      .Solid
    End With

Open in new window

0
 
Davy2270Commented:
try this code:
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With

The RGB codes for your specific colors can be discovered when you click the Fill button - More Colors - Custom tab. If you then click to a color and slide the intensity arrow on the right you will get the RGB code for the color.

Regards,
Davy
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now