Create and format a shape in worksheet by using VBA Excel

Add shapes on excel sheet 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

 

Add shapes on excel sheet using VBA excel

 

 

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

 

1 comment on “Create and format a shape in worksheet by using VBA Excel

Comments are closed.