Lovely ListBoxes - help!!
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
|