identifying multi-select items in a listbox

J

JT

I have a macro that fills a form with 2 listboxes (single). The user selects
1 items from each listbox to match.....Works great......The listbox is
populated from the data source (hidden sheet) in the workbook.

However, and isn't there always a however, the users have requested that the
matching process allow multiple items to be selected from each listbox.
Sounds easy enough but it is more challenging than I thought.

Once the items are selected, they are moved to a "new" sheet and then
deleted from the data source sheet and the form is re-populated with the
first visible item being the index + 1. I'm having trouble getting the
individual indexes when someone selects more than one. I have tried using
the code below but I'm not able to get the individual indexes.

Any suggestions would be greatly appreciated. Thanks for the
help....FYI....this is one of the first times I have programmed listboxes so
I think that is probably part of the problem....

Sub Find_LB1_Item()
q = 1
For SR1 = 0 To LB1.ListCount - 1
If LB1.Selected(SR1) = True Then
ReDim Preserve SelRef1(1 To q)
ReDim Preserve SelRow1(1 To q)
ReDim Preserve TI1(1 To q)

SelRef1(q) = LB1.List(SR1, 0)

If Len(LB1.List(SR1, 4)) = 0 Then
SelAmt1 = 0
Else
SelAmt1 = SelAmt1 + LB1.List(SR1, 4)
End If

FoundLB1 = True
SelRow1(q) = LB1.List(SR1, 5)
TI1(q) = LB1.ListIndex ' this line
q = q + 1

End If

Next SR1

End Sub
 
C

Chip Pearson

I think you want to use the Selected property of the ListBox. For example,

Dim N As Long
With Me.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
Debug.Print "Item " & CStr(N) & " was selected."
Else
Debug.Print "Item " & CStr(N) & " was not selected."
End If
Next N
End With

This assumes, of course, that you have set the MultiSelect property
appropriately.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top