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!)

Hi Zani,
Before adding items to the second Listbox, you might want to 'Clear'
all the existing items.

use

Me.ListBox2.Clear as first item in your ListBox1_Click() routine

and let me know if this works or not.

HTH
Phaedrus

Sometimes, If/then/else's can get confusing.

Luckily, there's an option to make it easier to follow: Select Case.

This kind of thing worked ok for me:

Option Explicit
Private Sub ListBox1_Change()
Dim myCol As Long
Dim i As Long

myCol = 999 'invalid column
Select Case LCase(Me.ListBox1.Value)
Case Is = "consumer": myCol = 3
Case Is = "gsk plc": myCol = 5
End Select

Me.ListBox2.Clear

If myCol = 999 Then
Me.ListBox2.AddItem "Invalid Choice"
Else
With Worksheets("Departments")
For i = 3 To .Cells(.Rows.Count, myCol).End(xlUp).Row
Me.ListBox2.AddItem Cells(i, myCol).Value
Next i
End With
End If

End Sub

Private Sub UserForm_Activate()
Dim i As Long
Me.ListBox1.Clear
Me.ListBox2.Clear
With Sheets("Departments")
For i = 3 To Cells(.Rows.Count, 1).End(xlUp).Row
Me.ListBox1.AddItem .Cells(i, 1).Value
Next i
End With
End Sub

===
But the only part that was necessary was that "listbox2.clear" line.

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

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

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

can you explain something?
sometimes i see
Case Is = "consumer":

sometimes

case = "consumer"

sometimes

case "consumer"


what's the difference?

thanks

--


Gary


"Dave Peterson" wrote in message
...
Sometimes, If/then/else's can get confusing.

Luckily, there's an option to make it easier to follow: Select Case.

This kind of thing worked ok for me:

Option Explicit
Private Sub ListBox1_Change()
Dim myCol As Long
Dim i As Long

myCol = 999 'invalid column
Select Case LCase(Me.ListBox1.Value)
Case Is = "consumer": myCol = 3
Case Is = "gsk plc": myCol = 5
End Select

Me.ListBox2.Clear

If myCol = 999 Then
Me.ListBox2.AddItem "Invalid Choice"
Else
With Worksheets("Departments")
For i = 3 To .Cells(.Rows.Count, myCol).End(xlUp).Row
Me.ListBox2.AddItem Cells(i, myCol).Value
Next i
End With
End If

End Sub

Private Sub UserForm_Activate()
Dim i As Long
Me.ListBox1.Clear
Me.ListBox2.Clear
With Sheets("Departments")
For i = 3 To Cells(.Rows.Count, 1).End(xlUp).Row
Me.ListBox1.AddItem .Cells(i, 1).Value
Next i
End With
End Sub

===
But the only part that was necessary was that "listbox2.clear" line.

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

I think it's just style/personal preference.

But I know I usually use "case is =" because I type:
Case is "consumer"
and get yelled at.
So I add an equal sign.




Gary Keramidas wrote:

can you explain something?
sometimes i see
Case Is = "consumer":

sometimes

case = "consumer"

sometimes

case "consumer"

what's the difference?

thanks

--

Gary

"Dave Peterson" wrote in message
...
Sometimes, If/then/else's can get confusing.

Luckily, there's an option to make it easier to follow: Select Case.

This kind of thing worked ok for me:

Option Explicit
Private Sub ListBox1_Change()
Dim myCol As Long
Dim i As Long

myCol = 999 'invalid column
Select Case LCase(Me.ListBox1.Value)
Case Is = "consumer": myCol = 3
Case Is = "gsk plc": myCol = 5
End Select

Me.ListBox2.Clear

If myCol = 999 Then
Me.ListBox2.AddItem "Invalid Choice"
Else
With Worksheets("Departments")
For i = 3 To .Cells(.Rows.Count, myCol).End(xlUp).Row
Me.ListBox2.AddItem Cells(i, myCol).Value
Next i
End With
End If

End Sub

Private Sub UserForm_Activate()
Dim i As Long
Me.ListBox1.Clear
Me.ListBox2.Clear
With Sheets("Departments")
For i = 3 To Cells(.Rows.Count, 1).End(xlUp).Row
Me.ListBox1.AddItem .Cells(i, 1).Value
Next i
End With
End Sub

===
But the only part that was necessary was that "listbox2.clear" line.

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

--

Dave Peterson