Update Command Button not Updating my Worksheet

[sspatriots]

I'm posting 4 codes that I have on this Userform of mine, because when I pull up a record using the "cmdFindPO_Click" code to pull up a record, I want to be able to edit one of the other fields and the select the "cmdUpdatePOData_Click" button to update the record in my worksheet. Right now, when I select the "cmdFindPO_Click" button nothing happens. Not sure what I have wrong here. Any advice would be greatly appreciated. Thanks, Steve

Private Sub cmdClearForm_Click()

    TextBox1.Text = ""
    TextBox2.Text = ""
    TextBox3.Text = ""
    TextBox4.Text = ""

End Sub

Private Sub cmdCreateNewPO_Click()

    Dim ws As Worksheet
    Dim LO As ListObject
    Dim Lastrow As Long
    Dim C As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set LO = ws.ListObjects("Table1")

With LO.Range.Columns(2) 'column_to_check is relative to the LO.Range
    Set C = .Find(what:="*", after:=.Cells(1), LookIn:=xlValues, _
        searchorder:=xlByRows, searchdirection:=xlPrevious)
        
    If Not C Is Nothing Then
    
        Debug.Print C.Row + 1 'last empty row
        
        TextBox1.Text = C.Offset(1, -1).Value
        TextBox1.Text = Format(TextBox1.Text, "0000")
      
        If C.Offset(1, -1).Value = "" Then
            MsgBox "There is no allocated job number available" & vbLf & _
                   "Please have additional Job numbers allocated." & vbLf & _
                   "Will now be exiting this form."
            Exit Sub
        Else
        'do stuff`
            Lastrow = C.Row + 1
            With ws
'                .Cells(Lastrow, 1).Value = TextBox1.Text
                .Cells(Lastrow, 2).Value = TextBox2.Text
                .Cells(Lastrow, 3).Value = TextBox3.Text
                .Cells(Lastrow, 4).Value = TextBox4.Text
            End With
        End If
    End If
End With
        
''''''        TextBox2.Value = ""
''''''        TextBox3.Value = ""
''''''        TextBox4.Value = ""

End Sub

Private Sub cmdFindPO_Click()
Dim PO_No As String
Dim Lastrow As Long
Dim i As Integer

PO_No = Trim(TextBox1.Text)
TextBox1.Text = Format(TextBox1.Text, "0000")

Lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To Lastrow    'The 2 means we are starting in the second row

If Worksheets("sheet1").Cells(i, "A").Value = PO_No Then

    TextBox2.Text = Worksheets("Sheet1").Cells(i, 2).Value
    TextBox3.Text = Worksheets("Sheet1").Cells(i, 3).Value
    TextBox4.Text = Worksheets("Sheet1").Cells(i, 4).Value

End If

Next




End Sub

Private Sub cmdUpdatePOData_Click()

    Dim ws As Worksheet
    Dim tb As ListObject
    Dim frm As Object    'UserForm
    Dim PO_No As String
    Dim i As Long
    Dim Lastrow As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set tb = ws.ListObjects("Table1")
    Set frm = FindUpdateorCreatePOUF
    
    With frm

    PO_No = Trim(.TextBox1.Text)
    TextBox1.Text = Format(TextBox1.Text, "0000")
    
      For i = 1 To tb.DataBodyRange.Rows.Count

          If tb.ListColumns("PO No").DataBodyRange.Cells(i).Value = PO_No Then
   
              tb.ListColumns("First Name").DataBodyRange.Cells(i).Value = .TextBox2.Text
              tb.ListColumns("Last Name").DataBodyRange.Cells(i).Value = .TextBox3.Text
              tb.ListColumns("Occupation").DataBodyRange.Cells(i).Value = .TextBox4.Text
        
        Exit For
         
            End If
      
      Next
   
   End With


End Sub

 

[NoSparks]

You don't need to look at every line of data in a loop to find the PO.
I'd use the Range.Find method to locate the PO directly

Private Sub cmdFindPO_Click()
    Dim ws As Worksheet
    Dim LO As ListObject
    Dim PO_No As String, POnum As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set LO = ws.ListObjects("Table1")
   
    'TextBox1.Text = Format(TextBox1.Text, "0000")   'should have been done with _beforeUpdate
                                                     'prior to exiting textbox1
    PO_No = Trim(TextBox1.Text)

With LO.Range.Columns(1)
    Set POnum = .Find(what:=PO_No, after:=.Cells(1), LookIn:=xlValues, _
                      searchorder:=xlByRows, searchdirection:=xlNext)
                     
    If Not POnum Is Nothing Then
        TextBox2 = POnum.Offset(, 1)
        TextBox3 = POnum.Offset(, 2)
        TextBox4 = POnum.Offset(, 3)
    Else
        MsgBox "Sorry, your PO_No was not found"
    End If
End With
End Sub

and for writing edits back to the table it would be the same idea only then the .Offsets = the TextBoxes

 

[sspatriots]

I don't have access to my file to try it out right now, but I'll give it a go in the morning. Would this work for the command button that updates the worksheet as well? For some reason, I could not get it to update the worksheet with that part of the code.

 

[NoSparks]

Would this work for the command button that updates the worksheet as well?

Yes, see the last line of my response, exactly same code except for those 3 lines needing flipped around.

 

[sspatriots]

That worked great. I did go back and stick a InputBox at the front end to put the PO number I want to find into the PO textbox for my users.

Thanks again for all your help....

Private Sub cmdFindPO_Click()

    Dim ws As Worksheet
    Dim tb As ListObject
    Dim PO_No As String, POnum As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set tb = ws.ListObjects("Table1")
    
    PO_No = InputBox("Enter PO Number")
    TextBox1.Text = PO_No
    
    PO_No = Trim(TextBox1.Text)

    With tb.Range.Columns(2)
    
        Set POnum = .Find(what:=PO_No, after:=.Cells(1), LookIn:=xlValues, _
                          searchorder:=xlByRows, searchdirection:=xlNext)
                         
        If Not POnum Is Nothing Then
        
            TextBox2 = POnum.Offset(, 1)
            TextBox3 = POnum.Offset(, 2)
            TextBox4 = POnum.Offset(, 3)
            
        Else
            MsgBox "Sorry, your PO_No was not found"
            
        End If
        
    End With
    
End Sub