I wrote the following code to store values written in a userform. To be
written to the correct cells in a spreadsheet when another command
button is clicked. The first time the comand button is clidcked it
works. After that I get a subscript out of range message. The array is
public and declared in a standard module.



Private Sub CommandButton1_Click()
Static counter As Integer

counter = counter + 1


ReDim Preserve myarray(counter, 1 To 2)


myarray(UBound(myarray, 1), 1) = TextBox1.Text
' This writes the contents of textbox1
' to the first column of the last row in the
' array.

myarray(UBound(myarray, 1), 2) = TextBox2.Text
' This writes the contents of textbox2
' to the second column of the last row in the
' array.

TextBox1.Text = ""

TextBox2.Text = ""

End Sub

You can't use redim preserve and change anything but the outermost dimension.

--
Regards,
Tom Ogilvy


"bobbo" wrote:

I wrote the following code to store values written in a userform. To be
written to the correct cells in a spreadsheet when another command
button is clicked. The first time the comand button is clidcked it
works. After that I get a subscript out of range message. The array is
public and declared in a standard module.



Private Sub CommandButton1_Click()
Static counter As Integer

counter = counter + 1


ReDim Preserve myarray(counter, 1 To 2)


myarray(UBound(myarray, 1), 1) = TextBox1.Text
' This writes the contents of textbox1
' to the first column of the last row in the
' array.

myarray(UBound(myarray, 1), 2) = TextBox2.Text
' This writes the contents of textbox2
' to the second column of the last row in the
' array.

TextBox1.Text = ""

TextBox2.Text = ""

End Sub