Solved: Excel User Forms with multiple selections

[Mavver]

Hello all

I want to develop a User Form in Excel with the ability to have the user choose multiple selections. For this I was going to use a List Box on the user form. Only trouble is, is that I have never done that before.

Rather than ask you busy people to help me do this every step of the way, can anyone point me to a website that can talk me through this? I have "some" vb knowledge, but not that much.

Thanks in advance

Mav

[Bob Phillips]

Make sure that you set the MultiSelect propeert of the ListBox.

This shows you how to retrieve from a multiselection



With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
Msgbox .List(i)
End If
Next i
End With

[Mavver]

Thanks for the pointer xld

I found this website

http://www.contextures.com/xlUserForm02.html

which gave an excellent rundown of the basics of a User Form.

and here is the code that I wrote down

Private Sub cmdAdd_Click()

Dim lRow As Long
Dim lUser As Long
Dim ws As Worksheet
Set ws = Worksheets("Knowledge")

lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
lUser = Me.cboUser.ListIndex

If Trim(Me.cboUser.Value) = "" Then
Me.cboUser.SetFocus
MsgBox "Please Select a User"
Exit Sub
End If

With ws
.Cells(lRow, 1).Value = Me.cboUser.Value
.Cells(lRow, 2).Value = Me.txtDate.Value
.Cells(lRow, 3).Value = Me.listLA.Value
.Cells(lRow, 4).Value = Me.txtCommentary.Value
.Cells(lRow, 5).Value = Me.cboKeyword1.Value
.Cells(lRow, 6).Value = Me.cboKeyword2.Value
.Cells(lRow, 7).Value = Me.cboKeyword3.Value
End With

Me.cboUser.Value = ""
Me.txtDate.Value = Format(Now, "Medium Date")
Me.listLA.Value = ""
Me.txtCommentary.Value = ""
Me.cboKeyword1.Value = ""
Me.cboKeyword2.Value = ""
Me.cboKeyword3.Value = ""
Me.cboUser.SetFocus

End Sub

Where would I place your bit of code to get the multiple selections from the list box?

Thanks in advance

Mav

[Bob Phillips]

A separate control, thus a separate procedure



Private Sub lstLA_Click()
With Me.lstLA
For i = 0 To .ListCount - 1
If .Selected(i) Then
Msgbox .List(i)
End If
Next i
End With
End Sub

[Mavver]

Thanks again xld, after reading your code and something that Ken Plus put on the Knowledge Base I can now see how this all works on the selection front.

The only trouble I am having now is not understanding where this selection has gone after my users have selected them. As far as I can tell the data has been selected but not inputted anywhere. So the next bit in this ongoing drama is to find out where these choices are placed in Excel after the user has selected them on the list box.

Sorry for taking so much of your time by asking these basic questions, but thank you in advance for any extra help.

Mav

[Bob Phillips]

They don't go anywhere, each selected item in the list has the selected property set as I showed. In my example, I just output a msgbox with the item value, you will have to process themn in your code.

[Mavver]

I managed to find some code which placed the results of the listbox into the spreadsheet, so I have marked this thread as solved and placed the code below

Thanks All

Mav


Dim lItem As Long
For lItem = 0 To listLA.ListCount - 1
If listLA.Selected(lItem) = True Then Sheet3.Range("c65536").End(xlUp)(2, 1) = listLA.List(lItem)
listLA.Selected(lItem) = False
End If
Next