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.

 

[dmt32]

Hi,
untested but try this update to your cmdUpdate_Click code & see if will do what you want

Private Sub cmdUpdate_Click()
    Dim arr() As Variant
    Dim lbAllocate As Object
    Dim i As Integer
    Dim RecordRow As Long
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Allocate")
    
    Set lbAllocate = Me.AllocateBox1
    RecordRow = lbAllocate.ListIndex
    
    If RecordRow <> -1 Then
        ReDim arr(0 To lbAllocate.ColumnCount)
        
        For i = 0 To UBound(arr)
            With Me.Controls("TextBox" & i + 1)
                arr(i) = .Value
                lbAllocate.List(lbAllocate.ListIndex, i) = .Value
            End With
        Next i
            
    ws.Cells(RecordRow + 2, 1).Resize(, UBound(arr)) = arr
    End If
            
End Sub

Dave

 

[vhdhfox]

Hi Dave,

Thank you for your help with this.

With Me.Controls("TextBox" & i + 1)

this line keeps come up with the below error

 

[dmt32]

I assume from your code you have 14 textboxes? Do you have same number of columns in your listbox?

Dave

 

[vhdhfox]

Yeah theres 14 columns

 

[vhdhfox]

Sorry 14 textboxes

 

[dmt32]

Check each TextBox in turn that they are named TextBox1, TextBox2 etc up to TextBox14

Dave

 

[vhdhfox]

yeah they're all named textbox1, textbox2 etc

 

[vhdhfox]

This is the layout if it helps.
also the only names I've changed are for the commandbutton (cmdUpdate) and Listbox (AllocateBox)

 

[dmt32]

try following updated codes

Private Sub cmdUpdate_Click()
    Dim arr() As Variant
    Dim lbAllocate As Object
    Dim i As Integer
    Dim RecordRow As Long
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Allocate")
    
    Set lbAllocate = Me.AllocateBox1
    RecordRow = lbAllocate.ListIndex
    
    If RecordRow <> -1 Then
        ReDim arr(1 To lbAllocate.ColumnCount)
        For i = 1 To UBound(arr)
            With Me.Controls("TextBox" & i)
                arr(i) = .Value
                lbAllocate.List(lbAllocate.ListIndex, i - 1) = .Value
            End With
        Next i
            
    ws.Cells(RecordRow + 2, 1).Resize(, UBound(arr)) = arr
    End If
            
End Sub

Sub UserForm_Initialize()
    
    Dim rngMultiColumn As Range
    Set rngMultiColumn = ThisWorkbook.Worksheets("Allocate").Range("A2:N13")
    With Me.AllocateBox1
        .ColumnCount = 14
        .ColumnWidths = "40;40;60;95;30;30;30;90;60;70;75;85;30"
        .List = rngMultiColumn.Cells.Value
    End With
    
End Sub

Dave