Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1949
  • Last Modified:

I want to write a macro for a shape in Excel

I have this circle (shape) that i inserted in excel and I want to write a macro that changes the inside color of this shape. I tried recording while i did it and that didn't work, i tried to record me inserting a shape and that didn't work either. Any ideas..... Is this possible
0
tchristie33
Asked:
tchristie33
1 Solution
 
kacorretiredCommented:
ActiveSheet.Shapes.AddShape(msoShapeOval, X0, Y0, 40, 40).Select
'to get the index of the last inserted shape
ShapesCount = ActiveSheet.Shapes.Count
'to color the circle
ActiveSheet.Shapes(ShapesCount).Fill.ForeColor.RGB = RGB(255,0,0)

I hope this help

wbr Janos
0
 
jeveristCommented:
Hi tchristie33,

You can check for the circle by type and or name and set the Interior.Color property like this:

Sub ChangeShapeColor()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
    If shp.Type = msoAutoShape Then
        If shp.Name = "Oval 1" Then
            shp.DrawingObject.Interior.Color = vbRed ' Red
        End If
    End If
Next shp

End Sub

Jim
0
 
kacorretiredCommented:
If you place the
for x=0 to 255
for y=o to 255
for z=0 to 255
    RGB(x,y,z)
    Do
        If Application.Wait(Now + TimeValue("0:00:1")) Then Exit Do 'wait for 1 sec
    Loop
next
next
next
you can change the wait time according your needs
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeroen RosinkCommented:
Kacor,
you madea typo in your code, instead of using 0 you used o.
for y=o to 255


0
 
kacorretiredCommented:
roos01,

you have right, sorry.I wrote it into comment field. But this is tested

Sub ShapeColorChange()
Dim X As Integer, Y As Integer, Z As Integer, ShapesCount As Integer
Dim X0 As Integer, Y0 As Integer
    'Exit on Esc
    X0 = 100
    Y0 = 400
    ActiveSheet.Shapes.AddShape(msoShapeOval, X0, Y0, 40, 40).Select
    'to get the index of the last inserted shape
    ShapesCount = ActiveSheet.Shapes.Count
    'to color the circle
    For X = 0 To 255 Step 16
        For Y = 0 To 255 Step 16
            For Z = 0 To 255 Step 16
                ActiveSheet.Shapes(ShapesCount).Fill.ForeColor.RGB = RGB(X, Y, Z)
                Do
                    If Application.Wait(Now + TimeValue("0:00:1")) Then Exit Do 'wait for 1 sec
                Loop
            Next
        Next
    Next
End Sub

wbr Janos
0
 
tchristie33Author Commented:
I am going to have to perform this for a  few shapes in my workbook. I wan to select a shape that I have already created and named and then turn it a color based on the criteria of another cell. Thanks for the help

Sub Shape()
Sheets("Performance-Reliability").Select
Dim shp As Shape
Shapes("Oval 1").Select
If Range("a1") < 25 Then
shp.DrawingObject.Interior.Color = vbRed
End If
Shapes("Oval 1").Select
If Range("a1") > 25 Then
shp.DrawingObject.Interior.Color = vbGreen
End If
Shapes("Oval 1").Select
If Range("a1") = 25 Then
shp.DrawingObject.Interior.Color = vbYellow
End If
End Sub
0
 
kacorretiredCommented:
If you know the name of the shape ("YourShape") you can simply change it's color by:

ActiveSheet.Shapes("YourShape").Fill.ForeColor.RGB = RGB(Red,Green,Blue)

where each variable of Red, Green and Blue have a value between 0 to 255
0
 
kacorretiredCommented:
you can use the Select Case statement. Use it on the sheet's macro page

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim shp As Shape
    Sheets("Performance-Reliability").Select
    With ActiveSheet.Shapes("Oval 1").DrawingObject.Interior
    Select Case Cells(1, 1).Value
        Case Is < 25
            .Color = vbRed
        Case Is > 25
            .Color = vbGreen
        Case Else
            .Color = vbYellow
    End Select

End Sub
0
 
tchristie33Author Commented:
I loved that it was easy and simple to understand
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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