?
Solved

UserForm Listbox Get All Items

Posted on 2011-04-21
13
Medium Priority
?
362 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

0
Comment
Question by:Cook09
  • 5
  • 5
  • 3
13 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35444075
On a quick view, shouldn't this

For lngIndex = 1 To .ListCount


be

For lngIndex = 0 To .ListCount -1

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35444085
Oh Plz Ignore it. You are subtracting it in the next line.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35444114
Cook, May I see your file?

Sid
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35444165
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35444178
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
0
 

Author Comment

by:Cook09
ID: 35444192
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35444204
>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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35444213
How are you using the function.

Can you show me the usage of that?

Sid
0
 

Author Comment

by:Cook09
ID: 35444264
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?
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 1000 total points
ID: 35444269
ListBox1.listindex=-1

Sid
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35444271
Yes, set ListIndex to -1.

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 1000 total points
ID: 35444280
But, better coding practice tells me you really need a new function with a new function name ;-)

Function GetAllItems(lstItems As MsForms.ListBox, Optional strDelimiter As String = ",") As String
   Dim lngIndex As Long, strData As String
   With lstItems
      For lngIndex = 1 To .ListCount
         strData = strData & strDelimiter & lstItems.List(lngIndex - 1)
      Next lngIndex
   End With
   GetAllItems = Mid$(strData, Len(strDelimiter) + 1)
End Function

Kevin
0
 

Author Closing Comment

by:Cook09
ID: 35444396
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
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
I came across an unsolved Outlook issue and here is my solution.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question