Listbox and Collections (Again!)

[John]

Hi there,

Yes I know the newsgroups are littered with this sort of question, but I
just can't find the answer.

I'm trying to fill the RowSource property with a collection ("List"), that
I've passed to another collection ("colNameForm"). The colNameForm
collection appears to be ok as the "Debug.Print v(0), v(1)" part chucks out
the correct two strings. It's only when I then get to the "Sub
LoadListboxes()" that I get a problem.

I'm getting a "Compile error: Arguement not optional"

Any clues?

Thanks

John

PS I want both parts to be in a 2 column listbox.


-----------------------------------------
'Module level
Dim colNameForm As New Collection
-----------------------------------------
Dim List As New Collection
Dim v As Variant
Dim item As Variant

Set arrFunctionForm = List
Set List = Nothing
For Each item In colNameForm
v = item
Debug.Print v(0), v(1)
Next item
-----------------------------------------

-----------------------------------------
Private Sub LoadListboxes()

Dim Ctrl As MSForms.Control

For Each Ctrl In Me.Controls
Select Case Ctrl.Name
Case "lstListBox1"
Ctrl.Clear
Ctrl.ColumnCount = 2
Ctrl.RowSource = colNameForm
Case "lstListBox2"
Case "lstListBox3"
End Select
Next Ctrl

 

[Tom Ogilvy]

You can only bind a Listbox to a range using rowsource. You will have to
add the items individually

This modification of your code worked for me:

Public colNameForm As Collection
Sub RemoveDuplicates()
Dim Rng As Range, Cell As Range
Dim v As Variant
Dim item As Variant
Dim List As New Collection

' The items are in A1:A10
Set Rng = Worksheets("Sheet3").Range("A1:A10")

On Error Resume Next
For Each Cell In Rng
v = List.item(Cell.Text)
If Err.Number <> 0 Then
v = Array(Cell.Text, Cell.Address(0, 0))
List.Add v, CStr(Cell.Value)
Else
v(1) = v(1) & "," & Cell.Address(0, 0)
List.Remove Cell.Text
List.Add v, CStr(Cell.Value)
End If
Err.Clear
Next Cell

' Resume normal error handling
On Error GoTo 0

Set colNameForm = List
' Print out the list is the Immediate window
' For Each item In colNameForm
' v = item
' Debug.Print v(0), v(1)
' Next item
UserForm1.Show

End Sub

in the USERFORM MODULE:

Private Sub UserForm_Initialize()


Dim Ctrl As MSForms.Control
Dim itm As Variant
For Each Ctrl In Me.Controls
Select Case Ctrl.Name
Case "lstListBox1"
Ctrl.Clear
Ctrl.ColumnCount = 2
For Each itm In colNameForm
Ctrl.AddItem itm(0)
Ctrl.List(Ctrl.ListCount - 1, 1) = itm(1)
Next
Case "lstListBox2"
Case "lstListBox3"
End Select
Next Ctrl

End Sub

 

[John]

Thanks for this Tom. I'll go away and study it and let you know how I got
on.

Best regards

John

 

[John]

Hi Tom,

Just to say thanks again. Worked perfectly. The only thing is that it's
raised another issue about listbox value length, but I'll write a new post
for that.

Thanks

John