To create a shape in worksheet and format by using VBA Excel.
Here we have added a rectangular shape in sheet 1, then we have modified the properties of the shape object.
What this code does
- Add a shape on Excel worksheet
- Rename a shape
- Position a shape on worksheet
- Change height and width of a shape
- Fill color in shape
- Change border color of the shape
- Make the border of the shape thick
- Add text in a shape
- Format the text in shape
- Change the color using RGB
- Change the color using color index
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