Excel VBA 2010 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 spreadsheet. 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, but the choices of shapes are still limited.

To have greater selection of shapes, you need to use the Excel VBA 2010 code. Excel VBA 2010 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:



 [Lesson 25][Table of Contents] 

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn