G
Guest
I have two list boxes on a userform, I want the values of the second list
box to be dependant on what is chosen in the first list box. Posted below is
the code I am using, which works, but when you run the userform and change
your mind on what selection you want in ListBox 1, it keeps adding the
entries into Listbox2 - can anybody show me the way around this! Eternally
grateful!
Private Sub UserForm_Activate()
Sheets("Departments").Activate
Dim i As Long
For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row
Me.ListBox1.AddItem Cells(i, 1).Value
Next i
End Sub
Private Sub ListBox1_Click()
If ListBox1.Value = "Consumer" Then
For i = 3 To Cells(Rows.Count, 3).End(xlUp).Row
Me.ListBox2.AddItem Cells(i, 3).Value
Next i
End If
If ListBox1.Value = "GSK plc" Then
For i = 3 To Cells(Rows.Count, 5).End(xlUp).Row
Me.ListBox2.AddItem Cells(i, 5).Value
Next i
End If
End Sub
box to be dependant on what is chosen in the first list box. Posted below is
the code I am using, which works, but when you run the userform and change
your mind on what selection you want in ListBox 1, it keeps adding the
entries into Listbox2 - can anybody show me the way around this! Eternally
grateful!
Private Sub UserForm_Activate()
Sheets("Departments").Activate
Dim i As Long
For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row
Me.ListBox1.AddItem Cells(i, 1).Value
Next i
End Sub
Private Sub ListBox1_Click()
If ListBox1.Value = "Consumer" Then
For i = 3 To Cells(Rows.Count, 3).End(xlUp).Row
Me.ListBox2.AddItem Cells(i, 3).Value
Next i
End If
If ListBox1.Value = "GSK plc" Then
For i = 3 To Cells(Rows.Count, 5).End(xlUp).Row
Me.ListBox2.AddItem Cells(i, 5).Value
Next i
End If
End Sub