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
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: