Excel Shapes

Thanks for the reply netcepter.

I've actually attached a screenshot of what I'm looking for. The macro to make this happen isn't a concern. If you notice, the example basically gives a visual of the given data. Now when the data gets updated the circles change colors, which isn't the question. Right now, the circles are on top of the cells and not inside. What would be the best way to reference them so I can get a particular dot to change color. So from the attached example, if I wanted to change the color of the circle in G16 from red to green, how should that be done?

I'm still a little confused. Did you create each cicle individually? If so, they each have specific names to them. e.g. oval 1, oval 2.  

<<"Now when the data gets updated the circles change colors, which isn't the question">>
I'm confused on that part.  Do you have a macro that changes colors?

One way to reference the circle name is

Sub Macro7()
    Dim crcl As String
     
    crcl = InputBox("Which Circle do you wish to change color?")
         
     'just to test the circle name
     ActiveSheet.Shapes(crcl).Select
   
End Sub

In otherwords if the circle in G16 in named "G16"  you would put G16 in the inputbox.


net

Ali,

If you are using Excel 2007 then there is built-in functionality with Conditional Formatting to do what you are asking.  See the graphics and the attached Excel file.  These conditional formatting icon sets are not available in Excel 2003 and you will need to follow NetCepter help if you are using that version.

The default for the icons is (but you can edit the rules to be any values you want):
Green >= 67
Yellow < 67 & >=33
Red < 33

Thanks ProdOps, that was exactly what I was looking for. That's a really good tip. I actually didn't know excel 2007 had these additional features. Unfortunately, at the moment I have 2003 and I have to make it work in that.

Netcepter - I did make each circle manually just to show what I wanted. What I meant when I said "Now when the data gets updated the circles change colors, which isn't the question" was that I can write the macro for that but that would be after I get the circle to appear where I want it. Sorry for the confusion. I liked your way of using the name of the circle but I wanted to make this on a larger scale and have the feature of automatically adding in a circle and the color accordingly. On a larger scale using the names of the circles can get very confusing thats why I wanted to use the cells as a refrence.

Here's how I'm thinking of doing it, please suggest if you can think of an improved version:

 Go through the chart labels on the grid (eg. car1, car2, etc.)
 Go through the given data.
 If there is a name match then look at the car size and the status.
 Look through a manually made legend on the screen with labels and circles.
 Copy paste the exact color circle under the right labels according to the status from the data.
 If there is no circle, add the circle in the right spot and assign the color. (This step is the tricky part)

I plan on completely clearing the grid of circles and re adding them everytime incase:
1) A new shape needs to be added for a different case. (A triangle may be used for all cars that receieved complaints.)
2) I only want to see a particular shape or color

From what I found I can select a cell and paste a shape and it always comes on top of that cell. This way I won't have to worry about each individual circle and would still have my data visually represented.

Thanks.
 
 

It's very interesting that Excel 2007 has kind of what your looking for.  Obviously there are others like you that wish to use shapes instead of cells. Thanks for the great info ProdOps

But since 2003 is what we have, there has to be an easier way.  Would you be a apposed to an input box asking for some data?  And then changing the circle color?.  When I have a chance I'll send you something before you go manual.

net

No problem Net...  Maybe the info will be useful to some other users who search the knowledgebase.   I'm guessing Microsoft got enough requests for this type of functionality they decided to write it into Excel 2007.

ProdOps

Thanks a lot netcepter. Although your example uses input boxes instead of data, it has enough information for a good understanding of using shapes. The only thing I'll add to complete the answer to my question is a way to place the circle. Using netcepters sheet, I modified the adding shape code and attached it. I added in an input box where it asks you which cell you want to add the circle to. Type in for example 'E13' and it should place the circle in that cell. This brings me to two questions:

1) AddShape(msoShapeOval, 270#, 255, 25.5, 24.75)  <---Can we add cell references instead of numbers for the placement?

2) What I'm doing using the cut and paste, is there a more efficient way?
 

    Dim s1 As Shape
    Dim nm As String
    Dim cell As String
    Dim acell As String
 
    
    
    acell = ActiveCell.Address
    nm = InputBox("Enter a name for your new shape.")
    cell = InputBox("Enter the exact cell of your new shape.")
     
     If nm <> "" Then
     
    Set s1 = ActiveSheet.Shapes.AddShape(msoShapeOval, 270#, 255, 25.5, 24.75)
    s1.Name = nm
    s1.Fill.ForeColor.SchemeColor = 10
    s1.Fill.Visible = msoTrue
    s1.Fill.Solid
    s1.Cut
    Sheet1.Range(cell).Select
    Paste
    Set s1 = Nothing
    Else
    Cancel = True
    End If

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

I've attached the a modified version of netcepters file to save time.

Thanks!