Working with shapes

Shapes_Working_withShapes.xlsm
Ctrl + D will copy a shape ..................Use the Arrow in the quick access tool bar to select a shape or many shapes.

Hidding shapes and pictures using VBA

ActiveSheet.Shapes("Picture 2").Visible = True
ActiveSheet.Shapes("Picture 2").Visible = False
ActiveSheet.Shapes("Rectangle: Rounded Corners 3").Visible = True
ActiveSheet.Shapes("Rectangle: Rounded Corners 3").Visible = False

Buttons created using shapes

Turning on and off a button or set of buttons using VBA. If all shapes are on sheet 1 then you can use the "With" command else you need to call out the sheet name each time.

Worksheets("Sheet1").Shapes.Range(Array("GeneralBlue")).Visible = msoTrue
Worksheets("Sheet1").Shapes.Range(Array("GeneralBlue")).Visible = msoFalse

or you could say:

Sub ShapeName()
With Sheet1

.Shapes.Range(Array("GeneralBlue")).Visible = msoTrue ' Visable
.Shapes.Range(Array("GeneralBlue")).Visible = msoFalse ' not Visable
end with
end sub


Examples:




Here is the macro:

Sub ShapeGeneral()
With Sheet1

.Shapes.Range(Array("GeneralBlue")).Visible = msoFalse
.Shapes.Range(Array("SalesBlue")).Visible = msoTrue
.Shapes.Range(Array("ProductBlue")).Visible = msoTrue
End With
End Sub


Sub ShapeSales()
With Sheet1

.Shapes.Range(Array("SalesBlue")).Visible = msoFalse
.Shapes.Range(Array("GeneralBlue")).Visible = msoTrue
.Shapes.Range(Array("ProductBlue")).Visible = msoTrue
End With
End Sub


Sub ShapeProduct()

With Sheet1
.Shapes.Range(Array("ProductBlue")).Visible = msoFalse
.Shapes.Range(Array("GeneralBlue")).Visible = msoTrue
.Shapes.Range(Array("SalesBlue")).Visible = msoTrue
End With

End Sub






Adding shapes to the worksheet. . . Shapes.AddShape Method (Excel)

Syntax expression. AddShape( _Type_ , _Left_ , _Top_ , _Width_ , _Height_ )

Sub addshape()

' ActiveSheet.Shapes.AddShape(msoShapeRectangle, 300, 30, 30, 30).Select
' ActiveSheet.Shapes.AddShape(1, 10, 10, 50, 50).Select
' Note OK to use shape Number or shape Name: AddShape(1, (is the same as) AddShape(msoShapeRectangle,

' ActiveSheet.Shapes.AddShape(msoShapeIsoscelesTriangle, 333, 32.25, 23.25, 42).Select
' ActiveSheet.Shapes.AddShape(msoShapeOval, 10, 50, 146.5, 146.5).Select

End Sub
Complete list of shape Numbers and Names

To move a rectangle one Column to the right use 49. To move a rectangle down one Row use 12.75
ActiveSheet.Shapes.AddShape(1, 49, 12.75, 50, 50).Select ' would place the rectangles top left corner at cell B2