This worked ok for me...

Dim StreetArray As Variant
Dim resp As Long
Dim sCtr As Long

If Worksheets("Macro").CheckBox1.Value = True Then
StreetArray = Array("Columbine", "Lois", "Poppy")
Else
sCtr = 0
ReDim StreetArray(1 To 1)
Do Until resp = vbNo
sCtr = sCtr + 1
ReDim Preserve StreetArray(1 To sCtr)
StreetArray(sCtr) _
= InputBox("Enter the name of the street you want to search.")
resp _
= MsgBox(Prompt:="Would you like to enter another StreetArray?", _
Buttons:=vbYesNo)
Loop
End If

But I don't think I'd use it. If the user hits the cancel button on the first
pass through in that loop, then you'll still be including that empty string in
the array you're building.

I think I'd do something like:

Dim StreetArray As Variant
Dim StreetName As String
Dim TestWks As Worksheet
Dim resp As Long
Dim sCtr As Long
Dim StreetArrayCreated As Boolean

StreetArrayCreated = False

sCtr = -1 'an indicator
If Worksheets("Macro").CheckBox1.Value = True Then
StreetArray = Array("Columbine", "Lois", "Poppy")
StreetArrayCreated = True
Else
sCtr = 0
ReDim StreetArray(1 To 1)
resp = vbYes
Do
StreetName _
= InputBox("Enter the name of the StreetArray you want to search.")
If Trim(StreetName) < "" Then
StreetArrayCreated = True
sCtr = sCtr + 1
ReDim Preserve StreetArray(1 To sCtr)
StreetArray(sCtr) = StreetName
End If
resp _
= MsgBox(Prompt:="Would you like to enter another StreetArray?", _
buttons:=vbYesNo)
Loop Until resp = vbNo
End If

If StreetArrayCreated = True Then
For sCtr = LBound(StreetArray) To UBound(StreetArray)
MsgBox StreetArray(sCtr)
Next sCtr
Else
MsgBox "no elements in StreetArray Array"
End If

I'm not sure what StreetArray (I changed the name) represents--but if it is a
collection of worksheet names, I'd add some more checking.

In fact, you may want to add a multiselect listbox to the worksheet that
displays all the possible streets so that you can just get the input that way.



ron wrote:

Here is an excerpt of a macro that processes an array of street names
later in the macro. The street names can either come from a set group
of street names or a user inputted street or group of streets.

z = 0

If Worksheets("Macro").CheckBox1.Value = True Then
street = Array(""Columbine", "Lois", "Poppy")
Else:
Do Until yy = 7 ' 7=no
z = z + 1
ReDim Preserve street(1 To z)
street(z) = InputBox("Enter the name of the street you
want to search.")
yy = MsgBox("Would you like to enter another street?",
vbYesNo)
Loop
End If

The above construction fails. The following modification allows it
run, but seens unwieldy. Is there a more efficient method, one that
would allow me to use the array "street" in both branches of the IF
statement?

z = 0

If Worksheets("Macro").CheckBox1.Value = True Then

mystreet = Array("Columbine", "Lois", "Poppy")

For z = 0 To 2
ReDim Preserve street(1 To z + 1)
street(z + 1) = mystreet(z)
Next
Else:
Do Until yy = 7 '7=no
z = z + 1
ReDim Preserve street(1 To z)

street(z) = InputBox("Enter the name of the street you
want to search.")
yy = MsgBox("Would you like to enter another street and
city?", vbYesNo)
Loop
End If

...TIA, Ron

--

Dave Peterson