Link to home
Start Free TrialLog in
Avatar of Cook09
Cook09Flag for United States of America

asked on

UserForm Listbox Get All Items

I have been using the Function below to extract items from a Listbox.  After extracting the items, they are then put within a comment of a cell.  Moving strData into a Comment works fine, the issue is that the Function does not always pick up all of the items that are placed within it.

If Items are just added from one ListBox1 to Listbox2 and then moved to the Cell Comment, it will pick up all of the items.  Because, the user also has the option to Remove an Item, if one of the items in ListBox2 has  been selected (highlighted) and then the procedure run, this function leaves the one Listbox item that is  highlighted from being recorded.

.ListCount correctly shows the number of items in Listbox2.  How do I modify this Function so that it will record all the Items whether one is highlighted or not?
Function GetSelectedItems(lstItems As MsForms.ListBox, Optional strDelimiter As String = ",") As String
   Dim lngIndex As Long, strData As String
   With lstItems
      For lngIndex = 1 To .ListCount
         If .Selected(lngIndex - 1) = False Then
            strData = strData & strDelimiter & lstItems.List(lngIndex - 1)
           End If
      Next lngIndex
   End With
   GetSelectedItems = Mid$(strData, Len(strDelimiter) + 1)
End Function

Open in new window

Avatar of SiddharthRout
SiddharthRout
Flag of India image

On a quick view, shouldn't this

For lngIndex = 1 To .ListCount


be

For lngIndex = 0 To .ListCount -1

Sid
Oh Plz Ignore it. You are subtracting it in the next line.

Sid
Cook, May I see your file?

Sid
Avatar of zorvek (Kevin Jones)
Some basic "is it plugged in" questions.

You are testing for NOT selected - is that intended?

Are you sure you are passing the correct list box control to the function?

Kevin
Just for fun ;-)

Function GetSelectedItems(lstItems As MsForms.ListBox, Optional strDelimiter As String = ",") As String
   Dim lngIndex As Long, strData As String
   Debug.Print lstItems.Name
   With lstItems
      For lngIndex = 1 To .ListCount
         If .Selected(lngIndex - 1) = False Then
            Debug.Print lstItems.List(lngIndex - 1)
            strData = strData & strDelimiter & lstItems.List(lngIndex - 1)
           End If
      Next lngIndex
   End With
   GetSelectedItems = Mid$(strData, Len(strDelimiter) + 1)
End Function

Kevin
Avatar of Cook09

ASKER

The file is rather large, and parts are confidential.  But, the .jpg should show the procedure of how it gets from ListBox1 to ListBox2.  As long as it is not "selected" the procedure works fine in taking it to a comment.
UserForm.jpg
>As long as it is not "selected" the procedure works fine in taking it to a comment.

Which is exactly how I perceive the code you have would work.

Kevin
How are you using the function.

Can you show me the usage of that?

Sid
Avatar of Cook09

ASKER

It takes the items from Listbox2 and builds a string array.

So:
strData ="Frozen Tissue Block(Lf),Cut Unstained-Coated,(lf},Cut Unstained-Plain"

When pasted into a Commented it looks like:

Frozen Tissue Block
Cut Unstained-Coated
Cut Unstained-Plain

Simple Concept, unless one is selected.  Is there a way to programmatically "Deselect" everything, prior to running the procedure?
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, set ListIndex to -1.

Kevin
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Cook09

ASKER

The quick approach was to set it to -1, however I appreciate the code where I may not have to worry about setting it to -1 in the future, should this occur again.

Thanks Guys