Transfer data from Userform Listbox to Worksheet

[vhdhfox]

Hi,

I have a userform with a listbox that I can use to edit data with multiple columns which works in regards to editing/adding data to the listbox but need a bit of help trying to transfer the data of the selected line that's been updated to the worksheet its pulling that data from.

Private Sub AllocateBox1_DblClick(ByVal Cancel As msforms.ReturnBoolean)
If AllocateBox1.ListIndex <> -1 Then
With AllocateBox1
TextBox1.Value = .List(.ListIndex, 0)
TextBox2.Value = .List(.ListIndex, 1)
TextBox3.Value = .List(.ListIndex, 2)
TextBox4.Value = .List(.ListIndex, 3)
TextBox5.Value = .List(.ListIndex, 4)
TextBox6.Value = .List(.ListIndex, 5)
TextBox7.Value = .List(.ListIndex, 6)
TextBox8.Value = .List(.ListIndex, 7)
TextBox9.Value = .List(.ListIndex, 8)
TextBox10.Value = .List(.ListIndex, 9)
TextBox11.Value = .List(.ListIndex, 10)
TextBox12.Value = .List(.ListIndex, 11)
TextBox13.Value = .List(.ListIndex, 12)
TextBox14.Value = .List(.ListIndex, 13)
TextBox1 = Format(TextBox1, "dd-mmm")
End With

End If

End Sub
Private Sub cmdUpdate_Click()

    If AllocateBox1.ListIndex <> -1 Then

With AllocateBox1
.List(.ListIndex, 0) = TextBox1.Value
.List(.ListIndex, 1) = TextBox2.Value
.List(.ListIndex, 2) = TextBox3.Value
.List(.ListIndex, 3) = TextBox4.Value
.List(.ListIndex, 4) = TextBox5.Value
.List(.ListIndex, 5) = TextBox6.Value
.List(.ListIndex, 6) = TextBox7.Value
.List(.ListIndex, 7) = TextBox8.Value
.List(.ListIndex, 8) = TextBox9.Value
.List(.ListIndex, 9) = TextBox10.Value
.List(.ListIndex, 10) = TextBox11.Value
.List(.ListIndex, 11) = TextBox12.Value
.List(.ListIndex, 12) = TextBox13.Value
.List(.ListIndex, 13) = TextBox14.Value

End With

End If

End Sub
Private Sub CommandButton1_Click()
Dim lRow As Long, ws As Worksheet
       Set ws = Sheets("Allocate")

    lRow = ws.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Row

    ws.Range(lRow).Value = getSelected(AllocateBox1)


End Sub

Sub UserForm_Initialize()

    Dim rngMultiColumn As Range
Set rngMultiColumn = ThisWorkbook.Worksheets("Allocate").Range("A2:O13")
With AllocateFitler.AllocateBox1
.ColumnWidths = "40;40;60;95;30;30;30;90;60;70;75;85;30"
.List = rngMultiColumn.Cells.Value
End With

End Sub
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

the getSelected function is what I've been playing around with to transfer only the selected data but haven't gotten it to work yet with the CommandButton1 code yet.
if theres anymore information needed for the worksheet or userform please let me know.

 

[vhdhfox]

that works perfectly! thank you so much.

 

[dmt32]

that works perfectly! thank you so much.

partly my error getting the indexing wrong but glad all resolved.

Dave