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

sspatriots

Well-known Member
Joined
Nov 22, 2011
Messages
585
Office Version
  1. 365
Platform
  1. Windows
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.


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

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Why .tb.ListColumns? Try using
VBA Code:
tb.ListColumns etc etc
 
Upvote 0
thanks, I just tried to remove the preceding ".", but I get the same error message.
 
Upvote 0
We are talking about the two lines tb.ListColumns("Job" & Chr(10) & "Status").DataBodyRange.Cells(i).Value = NewValue, isn't it?
Now you still get "Run-time error '424': Object required", or a different error on the same line?
 
Upvote 0
Add the debug.print block as follows:
VBA Code:
         If tb.ListColumns("Job Name").DataBodyRange.Cells(i).Value = job_name Then
            'Block to add >>
            On Error Resume Next
                Debug.Print ">>>"
                Debug.Print "A", tb.ListColumns("Job" & Chr(10) & "Status").DataBodyRange.Cells(i).Value
                Debug.Print "B", cboJobStatus.Text
                Debug.Print Err.Number, Err.Description
                Debug.Print "<<<"
            On Error GoTo 0
            '<< End of block
            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

The run the code; when you enter the debug mode open the vba Immediate window (typing Contr-g while in debug mode should do the job), copy what is written from >>> to <<< and attach to your next message.
 
Upvote 0
So now the problem is with cboJobStatus.Text
And indeed your macro doesn't set any reference to cboJobStatus; I guess you should use something like Me.ComboBox1.Text
 
Upvote 0
Not sure how to do that, but I will tell you that this userform has about 25 more textboxes that will be used to update the table data. I just provided 2 lines of fields that will be updated in my original code just to sort out the issue.
 
Upvote 0
You should look at the names of the controls you uses to set the values of the two columns
Since (I guess) this macro is within the useform code module you will use Me to refer to the userform and the name of the control to refer to the control
You can find these values by looking at the "properties" of the control, in the vba userform object module:
-select the control, look at the "name" property within the Property box (if you don't see the property box, try pressing F4)
 

Attachments

  • USERFORM_Immagine 2022-06-27 192937.jpg
    USERFORM_Immagine 2022-06-27 192937.jpg
    119.3 KB · Views: 6
Upvote 0

Forum statistics

Threads
1,223,969
Messages
6,175,691
Members
452,667
Latest member
vanessavalentino83

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
Back
Top