I have a user form with a textbox and two command buttons. The command
buttons are named Add and Done. When the user hits add I want the text in
the textbox to be added to a variable array. When the user hits the done
button I want to find each value in the array in the first column of the
active worksheet and replace it with the minimum value from the array.

How do I create this array?

How do I pass the array to the done button click event?

Private Sub Add_Click()
Dim a() As Variant

ReDim Preserve a(UBound(a) + 1)
a(UBound(a)) = textbox1.Text

End Sub

This gives me a subscript out of range message.

Declare the array only at the top of a module. If you only need to see it
in the Useform, then declare it at the top of the userform module. If you
need to see it elsewhere in the project, then declare it at the top of a
general module.

Dim a() As Variant


Private Sub Add_Click()
On Error Resume Next
ub = UBound(a)
If Err.Number < 0 Then
ReDim a(0 To 0)
a(UBound(a)) = TextBox1.Text
Else
ReDim Preserve a(UBound(a) + 1)
a(UBound(a)) = TextBox1.Text
End If
On Error goto 0
Label1.Caption = UBound(a)
End Sub


Private Sub Done_Click()
For i = LBound(a) To UBound(a)
Debug.Print i, a(i)
Next
End Sub


worked for me.


--
Regards,
Tom Ogilvy


"Rbp9ad" wrote in message
...
I have a user form with a textbox and two command buttons. The command
buttons are named Add and Done. When the user hits add I want the text in
the textbox to be added to a variable array. When the user hits the done
button I want to find each value in the array in the first column of the
active worksheet and replace it with the minimum value from the array.

How do I create this array?

How do I pass the array to the done button click event?

Private Sub Add_Click()
Dim a() As Variant

ReDim Preserve a(UBound(a) + 1)
a(UBound(a)) = textbox1.Text

End Sub

This gives me a subscript out of range message.

You can give this a try... I changed the variable to a static array of
strings, since you probably want the array to persist and a text box by
default contains strings...

Private Sub Add_Click()
Static a() As String
Dim lngArrayCounter As Integer

lngArrayCounter = -1
On Error Resume Next
lngArrayCounter = UBound(a)
On Error GoTo 0
If lngArrayCounter = -1 Then
ReDim Preserve a(0)
Else
ReDim Preserve a(lngArrayCounter + 1)
End If
a(UBound(a)) = textbox1.Text

End Sub
--
HTH...

Jim Thomlinson


"Rbp9ad" wrote:

I have a user form with a textbox and two command buttons. The command
buttons are named Add and Done. When the user hits add I want the text in
the textbox to be added to a variable array. When the user hits the done
button I want to find each value in the array in the first column of the
active worksheet and replace it with the minimum value from the array.

How do I create this array?

How do I pass the array to the done button click event?

Private Sub Add_Click()
Dim a() As Variant

ReDim Preserve a(UBound(a) + 1)
a(UBound(a)) = textbox1.Text

End Sub

This gives me a subscript out of range message.