Trying to reverse code that pulls data from table to make it write to the table

[sspatriots]

From all the help in the post below from "RoryA", I was able to successfully pull data into a Userform by referencing table column headers:

Is it possible to write code to modify code for new columns?

Now I have attempted to reverse the process to allow the user to select an "Update" button on the Userform and update the table directly from the form using the same code in reverse. However, it is giving me a "Run-time error '424': Object required" message at the line that starts with ".tb.ListColumns...". Not sure what object it is looking for here. Thanks in advance for any attention given to this post.

Sub Update_Job_Status_Form()


   Dim wb As Workbook
   Dim ws As Worksheet   'Added SPS,06/16/22
   Dim tb As ListObject
   Dim frm As Object    'UserForm
   Dim job_name As String
   Dim i As Long
   
   Set wb = ThisWorkbook
   
   Set ws = wb.Sheets("Jobs") 'Added SPS,06/16/22, worksheet the table is on
   
   Set tb = ws.ListObjects("G2JobList")
   
   Set frm = Job_Status
   
   With frm
   
      job_name = Trim(.txtJobName.Text)
      
      For i = 1 To tb.DataBodyRange.Rows.Count
      
         If tb.ListColumns("Job Name").DataBodyRange.Cells(i).Value = job_name Then
         
           
            .tb.ListColumns("Job" & Chr(10) & "Status").DataBodyRange.Cells(i).Value = cboJobStatus.Text
            .tb.ListColumns("G2" & Chr(10) & "PM").DataBodyRange.Cells(i).Value = txtG2PM.Text
            
            
            Exit For
         
         End If
      
      Next
   
   End With

NumLockCorrector

End Sub

 

[sspatriots]

Thanks for the information. I will have a look.

I made one more subtle change to those two lines and now it seems to work.

I changed this:

                    tb.ListColumns("Job" & Chr(10) & "Status").DataBodyRange.Cells(i).Value = cboJobStatus.Text
                    tb.ListColumns("G2" & Chr(10) & "PM").DataBodyRange.Cells(i).Value = txtG2PM.Text

To this:

                    tb.ListColumns("Job" & Chr(10) & "Status").DataBodyRange.Cells(i).Value = .cboJobStatus.Text
                    tb.ListColumns("G2" & Chr(10) & "PM").DataBodyRange.Cells(i).Value = .txtG2PM.Text

I added the dot in front of the combobox and text box references after the equals sign.


Hopefully that is all I needed. I will continue to fill up the rest of the code with the other textbox references that populate my tables and see how it goes. Thank you for your help. I liked how you use the "Debug.Print" to figure out where the problem was.

 

[Anthony47]

if cboJobStatus and txtG2PM are the names of the controls then the "dot" will link them to "With frm" (that hopefully refers to the userform), and that do the job.
(on the contrary we had to remove it in front of tb.ListColumns(etc etc because tb has nothing to do to "With frm")

 

[sspatriots]

Yea, when I reversed them from the original code that pulled the data from the table into the form, I left the dots there. Thanks,

 

[Anthony47]

Thank you for the feedback
You might want to review the use of With Statement, With statement (VBA)