Trying to update TextBox1 after a record is found when everything else is updated

[sspatriots]

I'm trying to get this code below to allow me to make a slight change to the value found in TextBox1 once the record is pulled up using a Find macro (below) first. It lets me edit every other field on the UserForm except for the one being searched on. I've tried several different approaches, but obviously I am missing something here. Right now if I try and change the value in TextBox1 and save the record to the worksheet I get the "Sorry, your PO_No was not found" message.

For example: If I did a Find on the PO number "788" (TextBox1) value, I would like to be able to change it to something like "788 R1" and select the Add/Update PO button on my form and change its value back to teh worksheet.

Any ideas would be greatly appreciated. Thanks, SS

Private Sub cmdFindPO_Click()

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

    With tb.Range.Columns(4)
    
        Set POnum = .Find(what:=PO_No, after:=.Cells(1), LookIn:=xlValues, _
                          searchorder:=xlByRows, searchdirection:=xlNext)
                         
        If Not POnum Is Nothing Then
            
            TextBox1 = POnum
            ComboBox2 = POnum.Offset(, 1)
            TextBox3 = POnum.Offset(, 2)
            TextBox4 = POnum.Offset(, 3)
            ComboBox5 = POnum.Offset(, 4)
            TextBox6 = POnum.Offset(, 5)
            TextBox7 = POnum.Offset(, 6)
            TextBox8 = POnum.Offset(, 7)
            ComboBox9 = POnum.Offset(, 8)
            TextBox10 = POnum.Offset(, 9)
            TextBox11 = POnum.Offset(, 10)
            TextBox12 = POnum.Offset(, 11)
        
        Else
            MsgBox "Sorry, your PO_No was not found"
            
        End If
        
    End With
    
End Sub

Private Sub cmdAddUpdatePOData_Click()

Dim ws As Worksheet
Dim tb As ListObject
Dim PO_No As String, POnum As Range

Set ws = ThisWorkbook.Sheets("2022")
Set tb = ws.ListObjects("Table46")
   
PO_No = Trim(TextBox1.Text)
    
    With Application
        .DisplayAlerts = False                'Turns off alerts
        .ScreenUpdating = False               'Turns off screen updating
    End With
   
    ShowHideLogSheet

    With tb.Range.Columns(4)
    
        Set POnum = .Find(what:=PO_No, after:=.Cells(1), LookIn:=xlValues, _
                          searchorder:=xlByRows, searchdirection:=xlNext)
                    
        If Not POnum Is Nothing Then
    
            POnum.Offset(, 1) = ComboBox2.Text
            POnum.Offset(, 2) = TextBox3.Text
            POnum.Offset(, 3) = TextBox4.Text
            POnum.Offset(, 4) = ComboBox5.Text
            POnum.Offset(, 5) = TextBox6.Text
            POnum.Offset(, 6) = TextBox7.Text
            POnum.Offset(, 7) = TextBox8.Text
            POnum.Offset(, 8) = ComboBox9.Text
            POnum.Offset(, 9) = TextBox10.Text
            POnum.Offset(, 10) = TextBox11.Text
            POnum.Offset(, 11) = TextBox12.Text
        
        Else
        
            MsgBox "Sorry, your PO_No was not found"
            
        End If
        
    End With

Repaint

    With Application
        .DisplayAlerts = True                'Turns on alerts
        .ScreenUpdating = True               'Turns on screen updating
    End With

End Sub

 

[sspatriots]

You should not need to repeat the Range Find process in your update code - did you try the updated solution I posted?

Dave

I haven't got that far yet. I'll give it a go later today. What I seems to work, but cleaning up what I have sounds better. Thanks, SS

 

[NoSparks]

No lines are being inserted.
When no PO is entered, the wild card is finding the first entry that isn't blank and overwrites it before you check if TextBox1 is blank.
You should be checking for TextBox1 being blank right at the beginning of the sub.

Private Sub cmdAddUpdatePOData_Click()
    If TextBox1.Text = "" Then
        MsgBox "No PO entered"
        Exit Sub
    End If
   ' rest on code

dmt32's suggestion works for me by adding your InputBox and removing the after:=.Cells(1), from the Find instruction.

Private Sub cmdFindPO_Click()
    
    PO_No = InputBox("Enter PO Number")
    TextBox1.Text = PO_No

    PO_No = Trim(TextBox1.Text)
    If Len(PO_No) = 0 Then Exit Sub
 
    Set POnum = tb.Range.Columns(4).Find(what:=PO_No, LookIn:=xlValues, lookat:=xlWhole, _
                                        searchorder:=xlByRows, searchdirection:=xlNext)
    'rest of code

 

[dmt32]

dmt32's suggestion works for me by adding your InputBox and removing the after:=.Cells(1), from the Find instruction.

Should not need the addition of an input box - the found range is held in memory as object variable is placed at TOP of the code page. Any update to value in TextBox1 should post to OPs table - I had also removed the After parameter in the Range.Find.

Dave

 

[dmt32]

I had also removed the After parameter in the Range.Find.

whoops - after another look, apparently I did not! but should have.

 

[NoSparks]

Dave, what originally populates TextBox1 ?

 

[dmt32]

Dave, what originally populates TextBox1 ?

I assumed that's where OP would enter original search value?

 

[NoSparks]

OK, thanks.
With the OP using an InputBox in their first post I didn't assume that.

 

[dmt32]

OK, thanks.
With the OP using an InputBox in their first post I didn't assume that.

I definitely deleted that part of the code as thought superfluous to what OP was doing.

I know I am getting old & a little daft but still puzzled how After parameter has been included when its not on my desktop copy that I thought I had posted - BTW - thanks for the catch.

Set POnum = tb.Range.Columns(4).Find(what:=PO_No, after:=.Cells(1), LookIn:=xlValues, lookat:=xlWhole, _
                                        searchorder:=xlByRows, searchdirection:=xlNext)

 

[sspatriots]

I definitely deleted that part of the code as thought superfluous to what OP was doing.

I know I am getting old & a little daft but still puzzled how After parameter has been included when its not on my desktop copy that I thought I had posted - BTW - thanks for the catch.

Set POnum = tb.Range.Columns(4).Find(what:=PO_No, after:=.Cells(1), LookIn:=xlValues, lookat:=xlWhole, _
                                        searchorder:=xlByRows, searchdirection:=xlNext)

I did go back and try and use your code as well in another copy of my workbook, however, I got a "Compile error: Invalid or unqualified reference" error and that ".Cells(1)" is highlighted.

 

[dmt32]

I did go back and try and use your code as well in another copy of my workbook, however, I got a "Compile error: Invalid or unqualified reference" error and that ".Cells(1)" is highlighted.

sorry my error thought I had deleted that part of code

try this corrected version

Private Sub cmdFindPO_Click()
    
    PO_No = Trim(TextBox1.Text)
    If Len(PO_No) = 0 Then Exit Sub
    
    Set POnum = tb.Range.Columns(4).Find(what:=PO_No, LookIn:=xlValues, lookat:=xlWhole, _
                                        searchorder:=xlByRows, searchdirection:=xlNext)
    
    If Not POnum Is Nothing Then
        
        TextBox1 = POnum
        ComboBox2 = POnum.Offset(, 1)
        TextBox3 = POnum.Offset(, 2)
        TextBox4 = POnum.Offset(, 3)
        ComboBox5 = POnum.Offset(, 4)
        TextBox6 = POnum.Offset(, 5)
        TextBox7 = POnum.Offset(, 6)
        TextBox8 = POnum.Offset(, 7)
        ComboBox9 = POnum.Offset(, 8)
        TextBox10 = POnum.Offset(, 9)
        TextBox11 = POnum.Offset(, 10)
        TextBox12 = POnum.Offset(, 11)
        
        cmdAddUpdatePOData.Enabled = True
        
    Else
        cmdAddUpdatePOData.Enabled = False
        MsgBox "Sorry, your PO_No was Not found", 48, "Not Found"
        
    End If
    
End Sub

Private Sub TextBox1_Change()
    Me.cmdAddUpdatePOData.Enabled = Not POnum Is Nothing And Len(Me.TextBox1.Value) > 0
End Sub

I have also included another code to prevent empty PO value being entered when updating table

Dave