I am working on a spread to track sales information. I have a userform that adds new data, but I want to make it easier to find and edit entries in the future as changes happen a lot. My data starts in A-G cell#5, with headers in row 4, with names Order Number; Date; Amount; GP%; Quote/Win/Loss; Customer; Comment. I have a userform with TextBox1-7 and three buttons, Update(cmdUpdate); Clear(cmdClr); Cancel(cmdExit). My current code is below. I would appreciate some help with getting it working right. I get errors but can't figure out what is wrong.
VBA Code:
Option Explicit
Private Sub UserForm_Initialize()
TextBox1.SetFocus
End Sub
Private Sub cmdClr_Click()
Dim ctl As Object
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then ctl.Value = Null
Next ctl
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
Findit
End If
End Sub
Private Sub Findit()
Dim fnd As Range
Dim Search As String
Dim sh As Worksheet
Dim i As Integer
Set sh = Sheet1
Search = TextBox1.Text
Set fnd = sh.Columns("A:A").Find(Search, , , xlWhole)
If fnd Is Nothing Then
MsgBox "No Quote Found", , "Error"
TextBox1.Text = ""
frmUserForm2.Hide
Else
For i = 2 To 7
frmUserForm2.Controls("UserFor2" & i).Text = sh.Cells(fnd.Row, i).Value
Next i
End If
End Sub
Private Sub cmdUpdate_Click()
Dim fnd As Range
Dim Search As String
Dim sh As Worksheet
Dim i As Integer
Dim ctl As Object
Set sh = Sheet2
Search = TextBox1.Text
Set fnd = sh.Columns("A:A").Find(Search, , , xlWhole)
For i = 2 To 7
sh.Cells(fnd.Row, i).Value = frmUserForm2.Controls("UserForm2" & i).Text
Next i
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then ctl.Value = Null
Next ctl
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub