Dependant Combo Box Problem

naisa113
04-17-2004, 12:51 PM
This is my first post...

Anyhow, I am in the process of creating dependant combo boxes and I am playing around with different styles. I started off using this piece of code...

Private Sub ComboBox4_GotFocus()
ComboBox5.Clear
Dim i As Integer
Dim myarray()
ReDim Preserve myarray(44)
temp = 1
For Each cel In Worksheets("Line1").Range("A3:A46").Cells
myarray(temp) = cel.Value
temp = temp + 1
Next cel
For t = 1 To temp - 2
p = t + 1
Start:
If p > UBound(myarray) Then
Exit For
End If
While (myarray(t) = myarray(p)) 'And Not (p > UBound(myarray))
For y = p To temp - 2
myarray(y) = myarray(y + 1)
Next y
ReDim Preserve myarray(temp - 1)
temp = temp - 1
If p > temp Then
GoTo Start
End If
Wend
Next t
ComboBox4.Clear


With ComboBox4

For i = 1 To UBound(myarray)
.AddItem myarray(i)
Next i
End With
End Sub
Private Sub ComboBox5_Change()
ComboBox6.Clear
End Sub
Private Sub ComboBox5_DropButtonClick()
'MsgBox (ComboBox4.Value)
Dim i As Integer
Dim myarray2()
Dim count As Integer
count = 0
temp = 1
ReDim Preserve myarray2(temp)

For Each cel In Worksheets("Line1").Range("A3:A46").Cells
If cel = ComboBox4.Value Then
ReDim Preserve myarray2(temp)
myarray2(temp) = count
temp = temp + 1
End If
count = count + 1
Next cel
count = 0
i = 1
Dim arr()

temp = 1
ReDim Preserve arr(temp)
For Each cel In Worksheets("Line1").Range("B3:B46").Cells
If temp > UBound(myarray2) Then
Exit For
End If
If count = myarray2(temp) Then
ReDim Preserve arr(temp)
arr(temp) = cel
temp = temp + 1
End If
count = count + 1
Next cel
For t = 1 To temp - 2
p = t + 1
Start:
If p > UBound(arr) Then
Exit For
End If
While (arr(t) = arr(p)) 'And Not (p > UBound(arr))
For y = p To temp - 2
arr(y) = arr(y + 1)
Next y
ReDim Preserve arr(temp - 1)
temp = temp - 1
If p > temp Then
GoTo Start
End If
Wend
Next t
ComboBox5.Clear


With ComboBox5

For i = 1 To UBound(arr)
.AddItem arr(i)
Next i
End With


'ComboBox4.Value = ""
End Sub

I'm not sure on how to get the second combo box to present a list depending on the first combo box selection. These boxes refer to a matrix that presents the 1st column with model numbers and and 2nd column with processes. What happens is the first box will display the I.D. for each model number contained but the second column only returns one process when there should be 4. Help. :confused:

MKoslof
04-19-2004, 06:48 AM
Just get the current combo box value. Basically, if you need to resort your array or build some sort of SQL statement around the current selection, use the Combo4.Text property to get the current value in Combo 4. Now, just take this value and act accordingly.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum