+ Reply to Thread
Results 1 to 2 of 2

transfering multi selected list box in userform to one cell in worksheet

  1. #1
    Registered User
    Join Date
    09-30-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    transfering multi selected list box in userform to one cell in worksheet

    Hi,
    I have created a userform and set it up with command button to transfer data to a worksheet. I have three list boxes in it that are currently under single select with the following command to transfer the info. How do I change this command so that when I multi select the items in each list box it will put it in the rows single cell with commas seperating the items.
    Please Login or Register  to view this content.
    Thanks

    Just updating is it possible to to do this within this command. I can create a seperate command for each box but would like to be able to do this with in this command button. would need to edit the following list boxes to go to those specific rows:
    ws.Cells(iRow, 13).Value = Me.Lbdirectcause.Value
    ws.Cells(iRow, 14).Value = Me.ListBox1.Value
    ws.Cells(iRow, 15).Value = Me.ListBox2.Value

    Does any one have any suggestions.
    Thanks!
    Last edited by isatrist; 10-01-2011 at 11:26 AM. Reason: Added Code Tags

  2. #2
    Registered User
    Join Date
    09-30-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: transfering multi selected list box in userform to one cell in worksheet

    Okay so I have my list boxes transfering over all selected items but I am still missing something. They are listing them in more than one cell when transfered. Please something help me edit the line so that the selected items show up in the same cell with commas, here is what I have now.

    Private Sub cmdevent_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Data")

    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

    'check for a part number
    If Trim(Me.txtpart.Value) = "" Then
    Me.txtpart.SetFocus
    MsgBox "Please enter a date"
    Exit Sub
    End If

    'copy the data to the database
    ws.Cells(iRow, 1).Value = Me.txtpart.Value
    ws.Cells(iRow, 2).Value = Me.txtlocation.Value
    ws.Cells(iRow, 3).Value = Me.txttime.Value
    ws.Cells(iRow, 4).Value = Me.cbobranch.Value
    ws.Cells(iRow, 5).Value = Me.txtclient.Value
    ws.Cells(iRow, 6).Value = Me.txtreportedby.Value
    ws.Cells(iRow, 7).Value = Me.txtreportedto.Value
    ws.Cells(iRow, 8).Value = Me.txtresponsiblemanager.Value
    ws.Cells(iRow, 9).Value = Me.cboeventclass.Value
    ws.Cells(iRow, 10).Value = Me.cbotypeofevent.Value
    ws.Cells(iRow, 11).Value = Me.cbowcbevent.Value
    ws.Cells(iRow, 12).Value = Me.txtrecap.Value
    ws.Cells(iRow, 16).Value = Me.txtaction.Value
    ws.Cells(iRow, 17).Value = Me.TextBox1.Value
    ws.Cells(iRow, 18).Value = Me.TextBox2.Value
    ws.Cells(iRow, 19).Value = Me.TextBox3.Value
    ws.Cells(iRow, 20).Value = Me.TextBox4.Value
    ws.Cells(iRow, 21).Value = Me.TextBox5.Value
    ws.Cells(iRow, 22).Value = Me.TextBox6.Value

    Dim lItem As Long

    For lItem = 0 To ListBox1.ListCount - 1

    If ListBox1.Selected(lItem) = True Then

    Sheet1.Range("N65536").End(xlUp)(2, 1) = ListBox1.List(lItem)

    ListBox1.Selected(lItem) = False

    End If

    Next

    For lItem = 0 To Lbdirectcause.ListCount - 1

    If Lbdirectcause.Selected(lItem) = True Then

    Sheet1.Range("M65536").End(xlUp)(2, 1) = Lbdirectcause.List(lItem)

    Lbdirectcause.Selected(lItem) = False

    End If

    Next

    For lItem = 0 To ListBox2.ListCount - 1

    If ListBox2.Selected(lItem) = True Then

    Sheet1.Range("O65536").End(xlUp)(2, 1) = ListBox2.List(lItem)

    ListBox2.Selected(lItem) = False

    End If

    Next

    'clear the data
    Me.txtpart.Value = ""
    Me.txtlocation.Value = ""
    Me.txttime.Value = ""
    Me.cbobranch.Value = ""
    Me.txtclient.Value = ""
    Me.txtreportedby.Value = ""
    Me.txtreportedto.Value = ""
    Me.txtresponsiblemanager.Value = ""
    Me.cboeventclass.Value = ""
    Me.cbotypeofevent.Value = ""
    Me.cbowcbevent.Value = ""
    Me.txtrecap.Value = ""
    Me.txtaction.Value = ""
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.TextBox6.Value = ""
    Me.txtpart.SetFocus

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1