Transfer listbox data in userform to worksheet

[Masoud]

Hi,
I'm trying to create a workbook to allow different disciplines to enter the data in the userfom and once they are finished, all data from userform to transfer to worksheet.
I used combobox and listbox (listbox allow the user to chose more than one item - in this example they called SWMS and PlantList). When I press insert, it doesnt transfer anything from listbox to worksheet. Can you please help me?

Private Sub UserForm_Initialize()
    
    With Sheets("Sheet1").Activate
    PlantList.RowSource = Range("F2:F150").Address
    End With
        
    With Sheets("Sheet1").Activate
    SWMS.RowSource = Range("G2:gG150").Address
    End With
    
    With Worksheets("Sheet1")
        Discipline.List = .Range("A2:A50" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    End With
    
    With Worksheets("Sheet1")
        CrewNumber.List = .Range("B2:B50" & .Range("B" & .Rows.Count).End(xlUp).Row).Value
    End With


    With Worksheets("Sheet1")
        CrewMob.List = .Range("C2:C50" & .Range("C" & .Rows.Count).End(xlUp).Row).Value
    End With


    With Worksheets("Sheet1")
        PlantMob.List = .Range("D2:D50" & .Range("D" & .Rows.Count).End(xlUp).Row).Value
    End With


    With Worksheets("Sheet1")
        Muck.List = .Range("E2:E50" & .Range("E" & .Rows.Count).End(xlUp).Row).Value
    End With
    With Worksheets("Sheet1")
        Track.List = .Range("h2:h50" & .Range("h" & .Rows.Count).End(xlUp).Row).Value
    End With
End Sub


Private Sub CommandButton1_Click()


    Dim LastRow As Long, ws As Worksheet


    Set ws = Sheets("Summary")


    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    ws.Range("A" & LastRow).Value = Discipline.Value
    ws.Range("C" & LastRow).Value = CrewNumber.Value
    ws.Range("F" & LastRow).Value = CrewMob.Value
    ws.Range("H" & LastRow).Value = PlantMob.Value
    ws.Range("I" & LastRow).Value = Muck.Value
    ws.Range("B" & LastRow).Value = Scope.Value
    ws.Range("G" & LastRow).Value = PlantList.Value
    ws.Range("E" & LastRow).Value = Resources.Value
    ws.Range("J" & LastRow).Value = Track.Value
    ws.Range("K" & LastRow).Value = from.Value
    ws.Range("L" & LastRow).Value = Trackto.Value
    ws.Range("M" & LastRow).Value = SWMS.Value
    
Unload Me
    
End Sub

 

[grawls44]

Hi,

Try

ws.Range("A" & LastRow).Value = Me.Discipline.Value

Hope that helps.

Greg

 

[Masoud]

Hi Greg,

Thank you for your help, however this doesnt work for ListBoxs. In my example following two lines are related to listbox:

ws.Range("M" & LastRow).Value = SWMS.Value
ws.Range("G" & LastRow).Value = PlantList.Value
[\code]

 

[sericom]

You need to cycle through all items in a listbox to see which lines are selected.
Try adding this function

Public Function getSelected(ByRef lb As msforms.ListBox) As String
    Dim txt As String, i As Integer
    txt = ""
    For i = 0 To lb.ListCount - 1
        If lb.Selected(i) = True Then
            txt = txt & lb.List(i) & vbNewLine
        End If
    Next
    getSelected = txt
End Function

And call it like

ws.Range("M" & LastRow).Value = getSelected(SWMS)

 

[Masoud]

Thank you very much. it works!