Transfer data from Userform Listbox to Worksheet

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
Hi,
untested but try this update to your cmdUpdate_Click code & see if will do what you want

VBA Code:
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

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
Hi Dave,

Thank you for your help with this.

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

this line keeps come up with the below error

1597912634717.png
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
I assume from your code you have 14 textboxes? Do you have same number of columns in your listbox?

Dave
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Yeah theres 14 columns

1597917662947.png
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Dave
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
yeah they're all named textbox1, textbox2 etc
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
This is the layout if it helps.
also the only names I've changed are for the commandbutton (cmdUpdate) and Listbox (AllocateBox)
1597922790346.png
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
try following updated codes

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,243
Messages
5,595,027
Members
413,960
Latest member
ikkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top