We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

UserForm Listbox Get All Items

Medium Priority
381 Views
Last Modified: 2012-05-11
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

Comment
Watch Question

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
CERTIFIED EXPERT
Top Expert 2008

Commented:
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
CERTIFIED EXPERT
Top Expert 2008

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2008

Commented:
>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

Author

Commented:
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?
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2008

Commented:
Yes, set ListIndex to -1.

Kevin
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.