Resources

    All about Visual Basic for Application (VBA) especially for Microsoft Excel.

Subscribe

  • Subscribe

How to select multiple shapes in excel VBA?

Posted by | July 11, 2012 .

“run-time error ’1004′ The index into the specified collection is out of bounds”, Why this error occurred?
The above error occurred during selecting 2 excel shapes which is circle and textbox. What I’m trying to do is creating circle with numbering inside without using add text function which available when we create circle. Why I’m not using this function? The reason is my numbering inside this circle is from 1 to 99 which is difficult to set very minimal size for the circle and uniform. If we use add text function for single digit 1 to 9 the circle look too big but when we set circle size according to single digit then 2 digit won’t fit. The only way we have to create 2 different shape. One circle and another is textbox, then later align these 2 shapes at center and middle.

The code to do the job for you as below:

Option Explicit
Sub CirclewithNumbering() 

Dim ObjCircle As Object
Dim ObjTxtBox As Object
Dim i As Integer 

For i = 1 To 20

   'Creating Circle
    ActiveSheet.Shapes.AddShape(msoShapeOval, 50, 20 + i * 30, 21.75, 21.75).Select
    Set ObjCircle = Selection.ShapeRange
    Selection.ShapeRange.Fill.Visible = msoFalse
    Selection.ShapeRange.Height = 21.75
    Selection.ShapeRange.Width = 21.75
    Selection.ShapeRange.Rotation = 0#
    Selection.ShapeRange.LockAspectRatio = msoTrue
    Selection.ShapeRange.Line.Weight = 1#
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)   

    'Create numbering
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 50, 20 + i * 30, 30, 21.75).Select
    Set ObjTxtBox = Selection.ShapeRange
    Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue
    Selection.Characters.Text = i
    With Selection.Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = xlHorizontal
        .AutoSize = True
    End With
    Selection.ShapeRange.Fill.Visible = msoFalse
    Selection.ShapeRange.Line.Visible = msoFalse   

    'To center the position of circle and numbering
    ActiveSheet.Shapes.Range(Array(ObjCircle.Name, ObjTxtBox.Name)).Select
    Selection.ShapeRange.Align msoAlignCenters, False
    Selection.ShapeRange.Align msoAlignMiddles, False
    Selection.Group
    Range("A1").Select
Next   
End Sub

The error happen when I did not assign name to these shape object and try to select in array. I mean select both at the same time and can be done by pressing Ctrl button while clicking at those object in excel.

Before:
Activesheet.Shapes.Range(Array(ObjCircle,ObjTxtBox)).Select

After:
Activesheet.Shapes.Range(Array(ObjCircle.Name,ObjTxtBox.Name)).Select

Hope this information can help others which is having the same problem.

If you have any question or project please e-mail to me, I will reply and create for free.
Click Here to E-mail

Thanks

Share Button
Share on Facebook
Bookmark this on Digg
Bookmark this on Yahoo Bookmark
Bookmark this on Google Bookmarks
Bookmark this on Delicious

Leave a Comment

If you would like to make a comment, please fill out the form below.

Name (required)

Email (required)

Website

Comments