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

S

sspatriots

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

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


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

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Appears to me that you are searching for the new value you've entered into textbox1 instead of the original value used to load your controls.

For your 788 to 788 R1 example given, I would use this
(the added space is to guarantee a space to split at)
VBA Code: 
PO_No = Split(Trim(Textbox1.Text) & " ", " ")(0)
and then write the new textbox1 to the found cell
 
Upvote 0
Use of wild card would also look after updates (788 to 788 R1 to 788 R2 etc.),
providing there is only one 788 in the column
VBA Code: 
    PO_No = Split(Trim(Textbox1.Text) & " ", " ")(0)

    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 = Textbox1.Text
            POnum.Offset(, 1) = ComboBox2.Text
            POnum.Offset(, 2) = TextBox3.Text
            'etc

        Else
            MsgBox "Sorry, your PO_No was not found"
        End If
        
    End With
 
Upvote 0
Solution
Hi,
as mentioned, your update PO_No would be searching for a value that does not exist - to do what you want, code needs to retain the found range

not fully tested by make a backup of your workbook & then replace your codes with All of following.

VBA Code: 
Dim ws              As Worksheet
Dim tb              As ListObject
Dim POnum           As Range
Dim PO_No           As String

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, after:=.Cells(1), LookIn:=xlValues, lookat:=xlWhole, _
                                        searchorder:=xlByRows, searchdirection:=xlNext)
 
    If Not POnum Is Nothing Then

        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 cmdAddUpdatePOData_Click()
 
    POnum.Value = Me.TextBox1.Text
    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
 
    MsgBox "PO: " & PO_No & Chr(10) & "Record Updated", 64, "Updated"
 
End Sub

Private Sub UserForm_Initialize()
    Set ws = ThisWorkbook.Sheets("2022")
    Set tb = ws.ListObjects("Table46")
    cmdAddUpdatePOData.Enabled = False
End Sub

Note the variables at the TOP. These MUST be placed at very TOP of your userforms code page OUTSIDE any procedure

Hope Helpful


Dave
 
Upvote 0
Excellent. That seems to do exactly what I needed. A lot better than what I had come up with... Thanks, SS
 
Upvote 0
NoSparks said:
Use of wild card would also look after updates (788 to 788 R1 to 788 R2 etc.),
providing there is only one 788 in the column
VBA Code: 
    PO_No = Split(Trim(Textbox1.Text) & " ", " ")(0)

    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 = Textbox1.Text
            POnum.Offset(, 1) = ComboBox2.Text
            POnum.Offset(, 2) = TextBox3.Text
            'etc

        Else
            MsgBox "Sorry, your PO_No was not found"
        End If
       
    End With
Click to expand...
When I tried putting the wildcard in that find part, the code would automatically insert a new blank row each time. The original one you posted is working for all scenarios so far. Thanks.
 
Upvote 0
sspatriots said:
When I tried putting the wildcard in that find part, the code would automatically insert a new blank row each time.
Click to expand...
That makes no sense to me.
Can you post the actual code that you are using instead of quoting my post
 
Upvote 0
NoSparks said:
That makes no sense to me.
Can you post the actual code that you are using instead of quoting my post
Click to expand...
I’ll post it in the morning when I get to work. I don’t have access to it right now. Thanks, SS
 
Upvote 0
NoSparks said:
That makes no sense to me.
Can you post the actual code that you are using instead of quoting my post
Click to expand...
Below is the original code before the space was added. You can see where I remarked it back out because it was inserting blank rows. I don't think that second "Else" part is doing anything. I had to add something for blank situations and I don't get that "Sorry, your PO_No was not found" message anymore. Not sure what I did to that.

VBA Code: 
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)
PO_No = Split(Trim(TextBox1.Text) & " ", " ")(0)    'Added per MrExcel message board, SPS, 09/29/22

    With Application
        .DisplayAlerts = False                'Turns off alerts
    '    .AlertBeforeOverwriting = False       'Turns off overwrite 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)           '& "*" took out because code is adding a row now
                         
        If Not POnum Is Nothing Then
            
            POnum = TextBox1.Text
                        
            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
  
            If TextBox1.Text = "" Then
        
                cmdFindPO_Click
    
            Else
  
            MsgBox "Sorry, your PO_No was not found"
            
            End If
            
        End If
    
    End With

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

Repaint

    With Application
        .DisplayAlerts = True                'Turns on alerts
    '    .AlertBeforeOverwriting = False       'Turns off overwrite alerts
        .ScreenUpdating = True               'Turns on screen updating
    End With

End Sub
 
Upvote 0
sspatriots said:
Below is the original code before the space was added. You can see where I remarked it back out because it was inserting blank rows. I don't think that second "Else" part is doing anything. I had to add something for blank situations and I don't get that "Sorry, your PO_No was not found" message anymore. Not sure what I did to that.
Click to expand...

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

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Update Command Button not Updating my Worksheet
Replies
4
Views
726
sspatriots
S
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
351
sspatriots
S
B
  • Question
Populating a worksheet based on a Combo Box value within a User Form
2 3 4
Replies
35
Views
1K
igold
igold
J
  • Solved
How to return focus to Userform textbox after process is finished.
Replies
1
Views
641
jvoss
J
Y
  • Question
sum total in specific column when it complete 1 quantity in column H
Replies
0
Views
282
yoon
Y

Forum statistics

Threads
1,215,368
Messages
6,124,521
Members
449,169
Latest member
mm424

Share this page

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