Hi
I have created a listbox and can transfer data from it to a table ion a sheet but it will only populate the last row of the table. Sometimes it may be require do insert/transfer the data to a row mid table ie if something had been forgotten and needed to be added later. I can drag the the row up but I would prefer to add at the specific row. Can't fid a way to do it.
eg Table is populated to row 10. I then want to transfer an item from the list box and insert it in row 5, but this code only allows it to go to row 11
here is my code
Private Sub Transfer_Click()
ActiveSheet.Unprotect Password:="123"
Dim wrksht As Worksheet
Dim objListObj As ListObject
Dim i As Long
Dim listObj As ListObject
Set listObj = Sheets("Quote Form").ListObjects("Table1")
listObj.ListRows.Add , 1
listObj.DataBodyRange(listObj.ListRows.Count, 1) = ListBox1.List(ListBox1.ListIndex)
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True And Me.ListBox1.List(i, 1) <> "" Then
'Sheet1.Range("A12345").End(xlUp).Offset(1, 0) = Me.ListBox1.List(i, 0)
Sheet1.Range("A12345").End(xlUp).Offset(0, 3) = Me.ListBox1.List(i, 1)
Sheet1.Range("A12345").End(xlUp).Offset(0, 4) = Me.ListBox1.List(i, 3)
Sheet1.Range("A12345").End(xlUp).Offset(0, 2) = Me.ListBox1.List(i, 2)
Sheet1.Range("A12345").End(xlUp).Offset(0, 6) = Me.ListBox1.List(i, 4)
For x = 1 To 4
'Sheet1.Range("A123").End(xlUp).Offset(0, 1) = Me.ListBox1.List(i, x)
'Sheet1.Range("Table1").End(xlUp).Offset(3, 3) = Me.ListBox1.List(i, 3)
Next x
End If
Next i
End Sub
Many thanks
I have created a listbox and can transfer data from it to a table ion a sheet but it will only populate the last row of the table. Sometimes it may be require do insert/transfer the data to a row mid table ie if something had been forgotten and needed to be added later. I can drag the the row up but I would prefer to add at the specific row. Can't fid a way to do it.
eg Table is populated to row 10. I then want to transfer an item from the list box and insert it in row 5, but this code only allows it to go to row 11
here is my code
Private Sub Transfer_Click()
ActiveSheet.Unprotect Password:="123"
Dim wrksht As Worksheet
Dim objListObj As ListObject
Dim i As Long
Dim listObj As ListObject
Set listObj = Sheets("Quote Form").ListObjects("Table1")
listObj.ListRows.Add , 1
listObj.DataBodyRange(listObj.ListRows.Count, 1) = ListBox1.List(ListBox1.ListIndex)
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True And Me.ListBox1.List(i, 1) <> "" Then
'Sheet1.Range("A12345").End(xlUp).Offset(1, 0) = Me.ListBox1.List(i, 0)
Sheet1.Range("A12345").End(xlUp).Offset(0, 3) = Me.ListBox1.List(i, 1)
Sheet1.Range("A12345").End(xlUp).Offset(0, 4) = Me.ListBox1.List(i, 3)
Sheet1.Range("A12345").End(xlUp).Offset(0, 2) = Me.ListBox1.List(i, 2)
Sheet1.Range("A12345").End(xlUp).Offset(0, 6) = Me.ListBox1.List(i, 4)
For x = 1 To 4
'Sheet1.Range("A123").End(xlUp).Offset(0, 1) = Me.ListBox1.List(i, x)
'Sheet1.Range("Table1").End(xlUp).Offset(3, 3) = Me.ListBox1.List(i, 3)
Next x
End If
Next i
End Sub
Many thanks