Create and format a shape in worksheet by using VBA Excel

Objective

To create a shape in worksheet and format by using VBA Excel.

Approach

Here we have added a rectangular shape in sheet 1, then we have modified the properties of the shape object.

What this code does

1. Add a shape on Excel worksheet
2. Rename a shape
3. Position a shape on worksheet
4. Change height and width of a shape
5. Fill color in shape
6. Change border color of the shape
7. Make the border of the shape thick
8. Add text in a shape
9. Format the text in shape
10. Change the color using RGB
11. Change the color using color index

 Code 

Sub AddShapes()
Dim MyShape As Shape
'Add a rectangle in the sheet1
Set MyShape = Sheets("sheet1").Shapes.AddShape(msoShapeRectangle, 50, 50, 100, 100)
'Give the shape a name
MyShape.Name = "MyRectangleShape"

'Format the shape appearance
With MyShape
    .Left = 200 ' Distance from Left
    .Top = 200  'Distance from Top
    .Height = 150 'Shape Height
    .Width = 250 'Shape Width
    .Fill.ForeColor.RGB = RGB(204, 255, 255) 'Shape Color
    .Line.ForeColor.RGB = RGB(255, 0, 0) 'Shape border color
    .Line.Weight = 10 'Border line thickness
End With

' Format the text in the shape
With MyShape.TextFrame
   .Characters.Text = "Add your text here" 'Text in the shape
   .Characters.Font.Bold = True         'Make text bold in the shape
   .Characters.Font.ColorIndex = 9      'Shape Text Color
  
End With
End Sub

As we can see in the image a rectangle with thick border is created on sheet 1 in the excel macro file. Using the similar approach, we can add other objects like charts, auto shapes etc.

Post you may like

Open and close excel file using VBA Excel