Cook09
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?
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
Oh Plz Ignore it. You are subtracting it in the next line.
Sid
Sid
Cook, May I see your file?
Sid
Sid
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
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
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
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
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
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
Can you show me the usage of that?
Sid
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, set ListIndex to -1.
Kevin
Kevin
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Thanks Guys
For lngIndex = 1 To .ListCount
be
For lngIndex = 0 To .ListCount -1
Sid