Thanks guys for the responses, really appreciated!

The first one is sufficient for what I need right now as this is a draft
prototype, but will keep your solution Dave for when I have to add in all the
rest, can see your point about a load of If/Then statements getting out of
hand!

Thanks again!
--
Zani
(if I have posted here, I really am stuck!)



"Dave Peterson" wrote:

ps. I changed the event to _change.

Zani wrote:

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
--
Zani
(if I have posted here, I really am stuck!)

--

Dave Peterson