ArrayList runtime error 1004

busybee_1_in · Apr 13th 2021

I have the following Macro that is activated when clicking a button.

Code

Sub ClickButton()


Dim sShape As Shape
Dim rowList As Object


Set rowList = CreateObject("System.Collections.ArrayList")


For Each sShape In ActiveSheet.Shapes
For j = 0 To 3
rowList.Add sShape.TopLeftCell.Row + j
Next j
rowList.Add sShape.BottomRightCell.Row
Next sShape


End Sub

Display More

However, when I open a worksheet and click a Button assigned this Macro I get a runtime 1004 error on the rowList.Add sShape.TopLeftCell.Row + j line. This error disappears when I click the button the second time.

Any suggestions on how to resolve this?

**Kenneth Hobson** · Apr 14th 2021

I can not duplicate your problem. I tried ActiveX and Forms command buttons and shape controls.

Code

Sub ClickButton()
  Dim sShape As Shape, rowList As Object, j As Integer
  
  Debug.Print ActiveSheet.Shapes.Count
  Set rowList = CreateObject("System.Collections.ArrayList")
    For Each sShape In ActiveSheet.Shapes
    For j = 0 To 3
      rowList.Add sShape.TopLeftCell.Row + j
    Next j
    rowList.Add sShape.BottomRightCell.Row
  Next sShape
  If rowList.Count > 0 Then MsgBox (Join(rowList.toarray, vbLf))
  
  Set rowList = Nothing
End Sub

Display More

ArrayList runtime error 1004

Participate now!

Share

Tags