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