How to update worksheet table data using userform listbox

[Megg]

I have a worksheet ("Breakdown") that consists of a table ("breakdownTable"). I have a button that opens a userform called "SearchForm" that displays a listbox. So far I have it so when I double click a selected item from this list box a second userform called "EditForm" displays that automatically inputs the details of the selected item into multiple textboxes.
I'm having trouble with getting the update button in the Editform to update both the listbox and the worksheet table. I've managed to get it to update the listbox but it won't do the same for the table.

This is what I have for the SearchForm Code:

Private Sub playerList_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
With EditForm
.TextBox1.text = Me.playerList.Column(1)
.TextBox2.text = Me.playerList.Column(0)
.TextBox3.text = Me.playerList.Column(2)
.TextBox4.text = Me.playerList.Column(3)
.TextBox5.text = Me.playerList.Column(4)
.TextBox6.text = Me.playerList.Column(6)
.TextBox7.text = Me.playerList.Column(7)

End With

EditForm.Show

End Sub

Private Sub UserForm_Initialize()
    
    playerList.List = Worksheets("Breakdown").ListObjects("breakdownTable").DataBodyRange.Value2
    playerList.ColumnCount = 9
    playerList.ColumnHeads = False
  
End Sub

And this is what I have for the EditForm:

Private Sub CommandButton1_Click()
Dim DBS As Range
Dim IND, X As Integer
Set wso = ThisWorkbook.Worksheets("Breakdown")

With SearchForm.playerList

.Column(1) = Me.TextBox1.text
.Column(0) = Me.TextBox2.text
.Column(2) = Me.TextBox3.text
.Column(3) = Me.TextBox4.text
.Column(4) = Me.TextBox5.text
.Column(6) = Me.TextBox6.text
.Column(7) = Me.TextBox7.text

Set DBS = wso.ListObjects("breakdownTable").DataBodyRange.End(x1Up)
For X = 1 To 9
DBS.Offset(1, X - 1) = .Column(X - 1)

IND = .ListIndex + 1
wso.Cells(IND, X) = .Column(X - 1)

Next X
Unload Me
End With

End Sub

I'm quite new to excel vba so any assistance is appreciated. I've tried to follow tutorials and looked at other forums but I'm just not quite understanding. I know it's something to do with the Set DBS and the rest below it in the EditForm code. The error I get is '1004' - Application-defined or object-defined error.

 

[dmt32]

Hi welcome to Forum

Generally speaking, you seldom need to create a separate userform to perform an edit function as this can normally be done from the main userform

However & sticking with what you are trying to do, see if these updates to your codes will do what you want

Make a backup of your workbook & then delete any relevant codes you have for searching & updating in your userforms code pages.

Place following codes in your SearchForm code page

Dim tblbreakdownTable    As ListObject
Public RecordRow         As Long

Private Sub playerList_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   EditForm.UpdateRecord Me, tblbreakdownTable
End Sub

Private Sub UserForm_Initialize()

    Set tblbreakdownTable = ThisWorkbook.Worksheets("Breakdown").ListObjects("breakdownTable")
   
    With playerList
        .List = tblbreakdownTable.DataBodyRange.Value2
        .ColumnCount = 9
        .ColumnHeads = False
    End With

End Sub

Note the variables at the top - these MUST be placed at very TOP of your Userforms code page OUTSIDE any procedures.

Place following codes in your EditForm code page

Private Sub cmdUpdate_Click()
    Me.Hide
End Sub

Private Sub cmdCancel_Click()
    cmdCancel.Tag = vbCancel
    Me.Hide
End Sub

Sub UpdateRecord(ByVal Form As Object, ByVal objTable As Object)
    Dim rngData     As Range
    Dim i           As Long
   
    With Form.playerList
        For i = 1 To 7
            Me.Controls("TextBox" & i).Text = .Column(Choose(i, 1, 0, 2, 3, 4, 6, 7))
        Next i
        Form.RecordRow = .ListIndex + 1
    End With
   
    Me.Show
   
    If Not Val(Me.cmdCancel.Tag) = vbCancel Then
       
        For i = 1 To 7
            'post record to table
            With Me.Controls("TextBox" & i)
                objTable.DataBodyRange.Cells(Form.RecordRow, Choose(i, 2, 1, 3, 4, 5, 7, 8)).Value = .Value
            End With
        Next i
       
        Set rngData = objTable.DataBodyRange
       
        'update listbox
        With Form.playerList
            .Clear
            .List = rngData.Value2:  .ListIndex = Form.RecordRow - 1
        End With
        'inform user
        MsgBox "Record Updated", 64, "Success"
    End If
   
    Unload Me
   
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = CloseMode = 0
    If Cancel Then Call cmdCancel_Click
End Sub

Note I have included two commandbuttons on this useform which should be named as follows

- cmdUpdate
- cmdCancel

I have only lightly tested & solution may need some adjustment but hopefully, updates will do what you want

Hope Helpful

Dave

 

[Megg]

That worked perfectly! Very helpful, thank you so much! I really appreciate it

 

[dmt32]

That worked perfectly! Very helpful, thank you so much! I really appreciate it

most welcome & appreciate your feedback

Dave