CODEWITHSUNDEEP
excelvba
Student of the Digital World
Student of the Digital World

Reputation: 3041

Adding selected Items to collection

I am trying to write selected values in a listbox inside a collection.

I get an error in this line - SelectedEnt.Add ItemReq, Key

Here is my complete code,

Sub YesFormDataSubmit()
    Dim SelectedEnt As Collection
    Dim Key As Variant

    Set SelectedEnt = New Collection
    Key = 0

    For lItem = 0 To Sheets("Main").Ent_ListBox.ListCount - 1
        If Sheets("Main").Ent_ListBox.Selected(lItem) = True Then
            ItemReq = Sheets("Main").Ent_ListBox.List(lItem)
            If ItemReq <> "" Then
                Key= Key + 1
                SelectedEnt.Add ItemReq, Key
            End If
        End If
    Next

End Sub

Upvotes: 0

Views: 535

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

Your error because of that Key must be String.

Sub YesFormDataSubmit()
    Dim SelectedEnt As Collection
    Dim Key As Variant

    Set SelectedEnt = New Collection
    Key = 0

    For lItem = 0 To Sheets("Main").Ent_ListBox.ListCount - 1
        If Sheets("Main").Ent_ListBox.Selected(lItem) = True Then
            ItemReq = Sheets("Main").Ent_ListBox.list(lItem)
            If ItemReq <> "" Then
                Key = Key + 1
               'key must be string
                SelectedEnt.Add ItemReq, CStr(Key)
            End If
        End If
    Next

End Sub

Upvotes: 1

David Zemens
David Zemens

Reputation: 53623

The key is not the index of a collection, consider:

Dim c as New Collection, i as Long
c.Add "David", "999"
c.Add "George", "14"

For i = 1 to c.Count
    Debug.Print c(i)
Next

The key must also be a string type, which is why you're getting a mismatch error, passing a long/integer instead of a string.

Unless you are using that key value for something else, you can omit it entirely.You can call them by index without using the key at all.

Just do SelectedEnt.Add ItemReq.

The items will be added to the collection in order, and will be accessible by index e.g., SelectedEnt.Item(1), etc.

If you do need key for some other reason, then you must explicitly cast it to string:

SelectedEnt.Add ItemReq, CStr(Key)

Upvotes: 2

Related Questions