HELP NEEDED: Using Multi Select List Boxes in a User Form

[epl108]

Hi everyone,
Still pretty new to VBA and kind of learning as I go and have come to a halt with one thing I'm trying to do. I have a form built for a team to input data about meetings they hold and I want them to be able to choose multiple items from a list of "meeting purposes" and not have to enter multiple entries for the same meeting to show that they actually discussed various topics. I have built the List Box into my form, and figured out how to clear it and set it to multi-select, but I cannot figure out how to get the data to save to my excel sheet when someone clicks "submit" on my form. All the other information from the form is saving correctly, but the column where "meeting purpose" should go remains blank whether i choose only 1 item or multiple items within the ListBox.

Any ideas how to get the data from the form into my data set?

Thanks a lot!

 

[tlowry]

Welcome to MrExcel!

Here's some code that might help

Private Sub CommandButton1_Click()
    Dim i, ssel
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then _
        ssel = ssel & vbCrLf & Me.ListBox1.List(i)
    Next i
    MsgBox ssel
End Sub

 

[epl108]

Welcome to MrExcel!

Here's some code that might help

Private Sub CommandButton1_Click()
    Dim i, ssel
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then _
        ssel = ssel & vbCrLf & Me.ListBox1.List(i)
    Next i
    MsgBox ssel
End Sub

Thanks so much for your response, tlowry. Will that work with the syntax I've been using (here's a excerpt of it):

Private Sub cmd_submit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("meetings")

iRow = ws.Cells.Find(what:="*", searchorder:=xlRows, _
Searchdirection:=xlPrevious, LookIn:=xlValues).Row + 1

.Cells(iRow, 14).Value = Me.txt_visitors_name.Value
.Cells(iRow, 17).Value = Me.combo_business_supported.Value
.Cells(iRow, 6).Value = Me.combo_event_type.Value
.Cells(iRow, 10).Value = Me.combo_gov_branch.Value
.Cells(iRow, 9).Value = Me.combo_gov_classification.Value
.Cells(iRow, 19).Value = Me.ListBox_Issue.Value

Those are 6 of the ~25 inputs the form asks for and as you can see some are text, some are combo boxes and now I'm trying to add a few List boxes. I am trying to use the code I pasted here to input each field to the correct column in my data sheet. In order to use the code you suggested, what modifications do I need to make?

Thanks again!

 

[tlowry]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--> It’s rather difficult to tell what you want to do, but here is some code to get a crlf separated list of what was selected in the multileveled list box:

Function GetMultSelLB(lb) As String

      Dim i, ssel
      For i = 0 To lb.ListCount - 1
          If lb.Selected(i) Then _
          ssel = ssel & vbCrLf & lb.List(i)
      Next i
      GetMultSelLB = ssel
  End Function

To put the selected text in a cell would follow the form:

Cells(1, 1) = GetMultSelLB(Me.ListBox1)

You will need to change the "Cells(1,1)" to where you want the text to go. And, you will need to change the name from "Me.Listbox1" to the "real" list box.