“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.
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