Dynamic Array - Combo Box

[mmouse1466]

I am creating a user form with combo selection boxes. One of my combo boxes
has a selection that is greater than the 25 items so I created a dynamic
array with using VB for the info lookup. So when the using goes to this combo
box it opens up a VB userform that has 3 combo boxes on it.

The array has 4 columns (column 1 = index number, column 2 = Port, column 3
= Crossing, column 4 = Terminal) and 650 rows.

I have it set up so when I select the Port "Atlanta" it shows in the next
combo box only the crossings associated with "Atlanta" say "Charleston" and
then the third Combo box only shows the terminals assoicated with
"Charleston".

Each combo box shows the appropriate associated info, but my problem is that
it also shows blank lines where other info would normally be in the array.
Is there a way to say not to show those blank lines? Or should I set up my
array differently?

A sample of my code:

Dim Array_Main(651, 4)
Dim Array_FO(21)
Dim Array_Port(651)
Dim Array_Term(651)


Private Sub cbo_Terminal_Change()
ActiveDocument.FormFields("Crossing").Result = cbo_Terminal.Value
End Sub


Private Sub cbo_FieldOffice_Change()
Erase Array_Port
Erase Array_Term

ActiveDocument.FormFields("FOData").Result = cbo_FieldOffice.Value

Dim x, i

i = 0

holder = ""

For x = LBound(Array_Main) To UBound(Array_Main)
If Array_Main(x, 2) <> holder And cbo_FieldOffice.Text =
Array_Main(x, 1) Then
Array_Port(i) = Array_Main(x, 2)
i = i + 1
End If
holder = Array_Main(x, 2)
Next

cbo_Port.List() = Array_Port
End Sub

Private Sub cbo_Port_Change()
Erase Array_Term

ActiveDocument.FormFields("FOData").Result = cbo_FieldOffice & " - " &
cbo_Port.Value

Dim x, i

i = 0

For x = LBound(Array_Main) To UBound(Array_Main) - 1
If Array_Main(x, 2) = cbo_Port.Text Then
Array_Term(i) = Array_Main(x, 3)
i = i + 1
End If
Next
cbo_Terminal.List() = Array_Term
End Sub


Private Sub CommandButton1_Click()

Unload Me

End Sub

Private Sub UserForm_Initialize()
Dim x, i, holder

'Make master array
'Field Office List
Array_Main(0, 1) = "Atlanta"
Array_Main(1, 1) = "Atlanta"
........
Array_Main(649, 1) = "Tucson"
Array_Main(650, 1) = "Tucson"

'Port of Entry List
Array_Main(0, 2) = "Atlanta"
Array_Main(1, 2) = "Atlanta"
........
Array_Main(649, 2) = "Sasabe"
Array_Main(650, 2) = "Tucson"

'Terminal List
Array_Main(0, 3) = "Atlanta"
Array_Main(1, 3) = "Atlanta Hartsfield-Jackson Int'l Airport"
........
Array_Main(649, 3) = "Sasabe"
Array_Main(650, 3) = "Tucson Int'l Airport"

'make Field Office Array

i = 0
holder = ""

For x = LBound(Array_Main) To UBound(Array_Main)
If Array_Main(x, 1) <> holder Then
Array_FO(i) = Array_Main(x, 1)
i = i + 1
End If
holder = Array_Main(x, 1)
Next x

cbo_FieldOffice.List() = Array_FO

End Sub

 

[Doug Robbins - Word MVP]

See:

http://groups.google.com/group/micr...doug's+combo+box+code&rnum=1#bf86a5657fe2c34e


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP