magrila
24-01-2011 22:28:05
Tenho um conteudo de uma planilha que precisava colocar em varios listbox de forma encadeada, procurando neste forum encontrei a solucao no topico combobox encadeado, ai eu so alterei para listbox que era o que eu desejava, porem surgiu um problema, talvez seja uma coisa simples porem nao acho o erro, e que os listbox so aceita texto e duas listbox preciso usar horas e outra numeros ,segue o codigo que autor Mauro Coutinho ( alias so muito grato ) deste forum deu como exemplo.
Private Sub UserForm_Initialize()
Dim OCOLLECTION As New Collection
Dim VARVALUE As Variant
Dim I As Long
On Error Resume Next
For Each VARVALUE In Plan1.Range("A2:A" & Plan1.Range("A65536").End(xlUp).Row)
OCOLLECTION.Add VARVALUE, VARVALUE
Next
For I = 1 To OCOLLECTION.Count
ComboBox1.AddItem OCOLLECTION.Item(I)
Next I
End Sub
'---------------------------------------------------------
Private Sub ComboBox1_Change()
Call PreencheCombo2
End Sub
Sub PreencheCombo2()
Dim OCOLLECTION As New Collection
Dim VARVALUE As Variant
Dim I As Long
Dim L As Long
L = 2
ComboBox2.Clear
On Error Resume Next
For Each VARVALUE In Plan1.Range("B2:B" & Plan1.Range("A65536").End(xlUp).Row)
If ComboBox1.Value = Plan1.Range("A" & L).Value Then
OCOLLECTION.Add VARVALUE, VARVALUE
End If
L = L + 1
Next
For I = 1 To OCOLLECTION.Count
ComboBox2.AddItem OCOLLECTION.Item(I)
Next I
End Sub
'---------------------------------------------------------
Private Sub ComboBox2_Change()
Call PreencheCombo3
End Sub
Sub PreencheCombo3()
Dim OCOLLECTION As New Collection
Dim VARVALUE As Variant
Dim I As Long
Dim L As Long
L = 2
ComboBox3.Clear
On Error Resume Next
For Each VARVALUE In Plan1.Range("C2:C" & Plan1.Range("A65536").End(xlUp).Row)
If ComboBox2.Value = Plan1.Range("B" & L).Value Then
OCOLLECTION.Add VARVALUE, VARVALUE
End If
L = L + 1
Next
For I = 1 To OCOLLECTION.Count
ComboBox3.AddItem OCOLLECTION.Item(I)
Next I
End Sub
'---------------------------------------------------------
Private Sub ComboBox3_Change()
Call PreencheCombo4
End Sub
Sub PreencheCombo4()
Dim OCOLLECTION As New Collection
Dim VARVALUE As Variant
Dim I As Long
Dim L As Long
L = 2
ComboBox4.Clear
On Error Resume Next
For Each VARVALUE In Plan1.Range("D2:D" & Plan1.Range("A65536").End(xlUp).Row)
If ComboBox3.Value = Plan1.Range("C" & L).Value Then
OCOLLECTION.Add VARVALUE, VARVALUE
End If
L = L + 1
Next
For I = 1 To OCOLLECTION.Count
ComboBox4.AddItem OCOLLECTION.Item(I)
Next I
End Sub
Eu So alterei para Listbox o resto ta igual ao codigo acima.
Private Sub UserForm_Initialize()
Dim OCOLLECTION As New Collection
Dim VARVALUE As Variant
Dim I As Long
On Error Resume Next
For Each VARVALUE In Plan1.Range("A2:A" & Plan1.Range("A65536").End(xlUp).Row)
OCOLLECTION.Add VARVALUE, VARVALUE
Next
For I = 1 To OCOLLECTION.Count
ComboBox1.AddItem OCOLLECTION.Item(I)
Next I
End Sub
'---------------------------------------------------------
Private Sub ComboBox1_Change()
Call PreencheCombo2
End Sub
Sub PreencheCombo2()
Dim OCOLLECTION As New Collection
Dim VARVALUE As Variant
Dim I As Long
Dim L As Long
L = 2
ComboBox2.Clear
On Error Resume Next
For Each VARVALUE In Plan1.Range("B2:B" & Plan1.Range("A65536").End(xlUp).Row)
If ComboBox1.Value = Plan1.Range("A" & L).Value Then
OCOLLECTION.Add VARVALUE, VARVALUE
End If
L = L + 1
Next
For I = 1 To OCOLLECTION.Count
ComboBox2.AddItem OCOLLECTION.Item(I)
Next I
End Sub
'---------------------------------------------------------
Private Sub ComboBox2_Change()
Call PreencheCombo3
End Sub
Sub PreencheCombo3()
Dim OCOLLECTION As New Collection
Dim VARVALUE As Variant
Dim I As Long
Dim L As Long
L = 2
ComboBox3.Clear
On Error Resume Next
For Each VARVALUE In Plan1.Range("C2:C" & Plan1.Range("A65536").End(xlUp).Row)
If ComboBox2.Value = Plan1.Range("B" & L).Value Then
OCOLLECTION.Add VARVALUE, VARVALUE
End If
L = L + 1
Next
For I = 1 To OCOLLECTION.Count
ComboBox3.AddItem OCOLLECTION.Item(I)
Next I
End Sub
'---------------------------------------------------------
Private Sub ComboBox3_Change()
Call PreencheCombo4
End Sub
Sub PreencheCombo4()
Dim OCOLLECTION As New Collection
Dim VARVALUE As Variant
Dim I As Long
Dim L As Long
L = 2
ComboBox4.Clear
On Error Resume Next
For Each VARVALUE In Plan1.Range("D2:D" & Plan1.Range("A65536").End(xlUp).Row)
If ComboBox3.Value = Plan1.Range("C" & L).Value Then
OCOLLECTION.Add VARVALUE, VARVALUE
End If
L = L + 1
Next
For I = 1 To OCOLLECTION.Count
ComboBox4.AddItem OCOLLECTION.Item(I)
Next I
End Sub
Eu So alterei para Listbox o resto ta igual ao codigo acima.