Excel 2010 VBA Lesson 26: Adding and Manipulating Shapes

 [Lesson 25]<<[Table of Contents] 

Adding shapes to a spreadsheet can provide better information and make a report more presentable. There are two ways to add a shape to a spreadsheet in Excel 2010 VBA. The first is to add the shape from the Insert menu of Excel 2010, as shown in Figure 26.1

vba2010_figure26.1






Although this method can let you insert shapes easily, the choices of shapes are still limited.

To have a greater selection of shapes, you need to use the Excel 2010  VBA  code. Excel 2010  VBA code allows you the more flexibility in manipulating the shapes. You can properties such as color, 3D effects, types of shapes and more to produce spectacular shapes.

The following is the code to draw a star-shape on the spreadsheet:

Private Sub CommandButton1_Click()
Dim wks As Worksheet
 Set wks = Worksheets("sheet1")
Dim myshape As Shape
 Set myshape = wks.Shapes.AddShape(msoShape10pointStar, 90, 90, 90, 60)
 myshape.TextFrame.Characters.Text = "My Shape"
 myshape.TextFrame.Characters.Font.Bold = True
With myshape.ThreeD
.Visible = True

.Depth = 60
.ExtrusionColor.RGB = RGB(255, 200, 255)
.PresetLightingDirection = msoLightingTop

End With
myshape.BottomRightCell = ""

End Sub

The Output Image is shown below:

figure_L25.1



 [Lesson 25]<<[Table of Contents]