MultiSelect Listbox

S

StevenD72

I have a userform I created that contains a multiselect listbox that gets
data from a worksheet "Personnel".

When I select the items I want in the listbox, it then is suppose to populate
a range of cells on another worksheet "Incident Report"

I am able to populate the listbox, select multiple items, and I have it so it
sends data to the worksheet across multiple columns... the only problem is
that it is sending the complete list and NOT the selected items. Can someone
please point me in the right direction? Thanks.


'---------------------------------------------------------------
Private Sub cmdAdd_Click()
Dim lPart As Long
Dim lItem As Long
Dim CellX As Long
Dim I As Long
Dim RangeRow As String
Dim ws As Worksheet
Set ws = Worksheets("Incident Report")

lPart = Me.cboTeam1.ListIndex
CellX = 27
I = 0
RangeRow = "A"
With Me.cboTeam1
For lItem = 0 To cboTeam1.ListCount - 1
If cboTeam1.Selected(lPart) = True Then
CellX = CellX + 1
I = I + 1
If CellX = 37 Then
CellX = 28
If I > 9 Then RangeRow = "F"
If I > 18 Then RangeRow = "K"
If I > 27 Then End
End If
Range(RangeRow & CellX).Value = cboTeam1.List(lItem, 1)
End If
Next lItem
End With


'clear the data
Me.cboTeam1.Value = ""
Me.cboTeam1.SetFocus

End Sub

'-----------------------------------------------------------------------------
---
 
S

Shasur

Hi Steve

I think the loop is checking if the last item is selected - lPart

Can you try if the following works for you

Private Sub cmdAdd_Click()
Dim lPart As Long
Dim lItem As Long
Dim CellX As Long
Dim I As Long
Dim RangeRow As String
Dim ws As Worksheet
Set ws = Worksheets("Incident Report")

lPart = Me.cboTeam1.ListIndex
CellX = 27
I = 0
RangeRow = "A"
With Me.cboTeam1
For lItem = 0 To cboTeam1.ListCount - 1
' If cboTeam1.Selected(lPart) = True Then // commented by shasur
If cboTeam1.Selected(lItem) = True Then '// added by by shasur
CellX = CellX + 1
I = I + 1
If CellX = 37 Then
CellX = 28
If I > 9 Then RangeRow = "F"
If I > 18 Then RangeRow = "K"
If I > 27 Then End
End If
Range(RangeRow & CellX).Value = cboTeam1.List(lItem, 1)
End If
Next lItem
End With

Cheers
Shasur
 
S

StevenD72

Thanks Shasur,

That worked perfectly.
I have been beating my brain for hours and didn't even see that.

Steve
Hi Steve

I think the loop is checking if the last item is selected - lPart

Can you try if the following works for you

Private Sub cmdAdd_Click()
Dim lPart As Long
Dim lItem As Long
Dim CellX As Long
Dim I As Long
Dim RangeRow As String
Dim ws As Worksheet
Set ws = Worksheets("Incident Report")

lPart = Me.cboTeam1.ListIndex
CellX = 27
I = 0
RangeRow = "A"
With Me.cboTeam1
For lItem = 0 To cboTeam1.ListCount - 1
' If cboTeam1.Selected(lPart) = True Then // commented by shasur
If cboTeam1.Selected(lItem) = True Then '// added by by shasur
CellX = CellX + 1
I = I + 1
If CellX = 37 Then
CellX = 28
If I > 9 Then RangeRow = "F"
If I > 18 Then RangeRow = "K"
If I > 27 Then End
End If
Range(RangeRow & CellX).Value = cboTeam1.List(lItem, 1)
End If
Next lItem
End With

Cheers
Shasur
I have a userform I created that contains a multiselect listbox that gets
data from a worksheet "Personnel".
[quoted text clipped - 45 lines]
'-----------------------------------------------------------------------------
---
 

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