Dim lrow As Long
Dim activeRow As Long
Private Sub cmdExit_Click()
Dim iExit As VbMsgBoxResult
iExit = MsgBox("Do you want to exit the Application?", vbQuestion + vbYesNo, "Data Entry System")
If iExit = vbYes Then
Unload Me
End If
End Sub
Private Sub cmdNext_Click()
Dim FindRow
Dim cRow As String
On Error Resume Next
cRow = Me.control0.Value
Set FindRow = Sheet3.Range("A:A").Find(What:=cRow, LookIn:=xlValues).Offset(1, 0)
If FindRow.Value = "" Then Exit Sub
Me.txtSearch = FindRow.Value
cNum = 23
For x = 0 To cNum
Me.Controls("Control" & x).Value = FindRow
Set FindRow = FindRow.Offset(0, 1)
Next
On Error GoTo 0
TextBox14.Value = activeRow
End Sub
Private Sub cmdPrevious_Click()
Dim FindRow
Dim cRow As String
On Error Resume Next
cRow = Me.control0.Value
Set FindRow = Sheet3.Range("A:A").Find(What:=cRow, LookIn:=xlValues).Offset(-1, 0)
If FindRow.Value = Sheet3.Range("A1").Value Then Exit Sub
Me.txtSearch = FindRow.Value
cNum = 23
For x = 0 To cNum
Me.Controls("Control" & x).Value = FindRow
Set FindRow = FindRow.Offset(0, 1)
Next
On Error GoTo 0
TextBox14.Value = activeRow
End Sub
Private Sub cmdPrint_Click()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
LastRow = ws.Cells(ws.Rows.Count, "N").End(xlUp).Row
ws.PageSetup.PrintArea = "A1:N" & LastRow + 1
Next ws
Application.Dialogs(xlDialogPrinterSetup).Show
ThisWorkbook.Sheets("Inventory").PrintOut copies:=1
End Sub
Private Sub cmdSearch_Click()
Dim FindRow
Dim cRow As String
On Error GoTo errHandler
cRow = Me.txtSearch.Value
Set FindRow = Sheet3.Range("A:A").Find(What:=cRow, LookIn:=xlValues)
For x = 0 To 23
Me.Controls("control" & x).Value = FindRow
Set FindRow = FindRow.Offset(0, 1)
Next
On Error GoTo 0
Exit Sub
errHandler:
MsgBox " Error!.Reference Does not Exist!" & vbCrLf & Err.Description
End Sub
Private Sub cmdSave_Click()
Call UnProtectSheet
lrow = Sheets("Inventory").Range("A" & Rows.Count).End(xlUp).Row + 1
If MsgBox("Do you wnat to Add a new Record?", vbYesNo + vbQuestion, "Question") = vbNo Then
Exit Sub
End If
Sheets("Inventory").Cells(lrow, "A").Value = Me.control0.Value
Sheets("Inventory").Cells(lrow, "B").Value = Me.control1.Value
Sheets("Inventory").Cells(lrow, "C").Value = Me.control2.Value
Sheets("Inventory").Cells(lrow, "D").Value = Me.control3.Value
Sheets("Inventory").Cells(lrow, "E").Value = Me.control4.Value
Sheets("Inventory").Cells(lrow, "F").Value = Me.control5.Value
Sheets("Inventory").Cells(lrow, "G").Value = Me.control6.Value
Sheets("Inventory").Cells(lrow, "H").Value = Me.control7.Value
Sheets("Inventory").Cells(lrow, "J").Value = Me.control9.Value
Sheets("Inventory").Cells(lrow, "M").Value = Me.control12.Value
Sheets("Inventory").Cells(lrow, "N").Value = Me.control13.Value
Sheets("Inventory").Cells(lrow, "P").Value = Me.control15.Value
MsgBox "Record Added!"
Call ProtectSheet
Call Reset
End Sub
Function Reset()
txtSearch = ""
Me.control0 = ""
Me.control1 = ""
Me.control2 = ""
Me.control3 = ""
Me.control4 = ""
Me.control6 = ""
Me.control7 = ""
Me.control8 = ""
Me.control9 = ""
Me.control10 = ""
Me.control11 = ""
Me.control12 = ""
Me.control13 = ""
Me.control14 = ""
Me.control15 = ""
Me.control16 = ""
Me.control17 = ""
Me.control19 = ""
Me.control20 = ""
End Function
Private Sub cmdClear_Click()
txtSearch = ""
Me.control0 = ""
Me.control1 = ""
Me.control2 = ""
Me.control3 = ""
Me.control4 = ""
Me.control6 = ""
Me.control7 = ""
Me.control8 = ""
Me.control9 = ""
Me.control10 = ""
Me.control11 = ""
Me.control12 = ""
Me.control13 = ""
Me.control14 = ""
Me.control15 = ""
Me.control16 = ""
Me.control17 = ""
Me.control19 = ""
Me.control20 = ""
MsgBox "Add New record"
End Sub
Private Sub UserForm_Initialize()
Dim cntr As Integer
control21.Value = Application.UserName
control5 = Format(Now(), "dd/mmm/yyyy")
control22 = Format(Now(), "dd/mmm/yyyy")
cntr = Application.WorksheetFunction.CountA(Range("AJ:AJ"))
For i = 1 To cntr
Me.control1.AddItem Cells(i, 36)
Next i
Me.control2.AddItem "6"
Me.control2.AddItem "8"
Me.control2.AddItem "10"
Me.control2.AddItem "12"
Me.control2.AddItem "14"
Me.control2.AddItem "16"
Me.control2.AddItem "XS"
Me.control2.AddItem "M"
Me.control2.AddItem "L"
Me.control2.AddItem "XL"
With Me.control15
.AddItem "MIKE"
.AddItem "PAUL"
End With
End Sub
Private Sub cmdUpdate_Click()
Call UnProtectSheet
Dim REFERENCE_NUMBER As String
control22.Text = Date
REFERENCE_NUMBER = Trim(control0.Text)
LastRow = Worksheets("Inventory").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
If Worksheets("Inventory").Cells(i, 1).Value = REFERENCE_NUMBER Then
If MsgBox("Do you Want to Update Record ?", vbYesNo + vbQuestion, "Question") = vbNo Then
Exit Sub
End If
Worksheets("Inventory").Cells(i, 23) = Format(Now(), "dd/mmm/yyyy")
Worksheets("Inventory").Cells(i, 1).Value = control0.Text
Worksheets("Inventory").Cells(i, 2).Value = control1.Text
Worksheets("Inventory").Cells(i, 3).Value = control2.Text
Worksheets("Inventory").Cells(i, 4).Value = control3.Text
Worksheets("Inventory").Cells(i, 5).Value = control4.Text
Worksheets("Inventory").Cells(i, 6).Value = control5.Text
Worksheets("Inventory").Cells(i, 7).Value = control6.Text
Worksheets("Inventory").Cells(i, 8).Value = control7.Text
Worksheets("Inventory").Cells(i, 10).Value = control9.Text
Worksheets("Inventory").Cells(i, 13).Value = control12.Text
Worksheets("Inventory").Cells(i, 14).Value = control13.Text
Worksheets("Inventory").Cells(i, 16).Value = control15.Text
MsgBox "Record Updated!"
End If
Next
Call ProtectSheet
Call Reset2
End Sub
Function Reset2()
txtSearch = ""
Me.control0 = ""
Me.control1 = ""
Me.control2 = ""
Me.control3 = ""
Me.control4 = ""
Me.control6 = ""
Me.control7 = ""
Me.control8 = ""
Me.control9 = ""
Me.control10 = ""
Me.control11 = ""
Me.control12 = ""
Me.control13 = ""
Me.control14 = ""
Me.control15 = ""
Me.control16 = ""
Me.control17 = ""
Me.control19 = ""
Me.control20 = ""
End Function
Private Sub control7_Change()
control7.Value = Format(control7.Value, "$#,##0")
End Sub
Private Sub control6_Change()
control6.Value = Format(control6.Value, "$#,##0")
End Sub
Private Sub control8_Change()
control8.Value = Format(control8.Value, "$#,##0")
End Sub
Private Sub txtSearch_Change()
Me.txtSearch = UCase(Me.txtSearch)
End Sub
Private Sub control0_Change()
Me.control0 = UCase(Me.control0)
End Sub
Private Sub control2_Change()
Me.control2 = UCase(Me.control2)
End Sub
Private Sub control3_Change()
Me.control3 = UCase(Me.control3)
End Sub
Private Sub control5_Change()
Me.control5 = UCase(Me.control5)
End Sub
Private Sub txtCustomer_Change()
Me.txtCustomer = UCase(Me.txtCustomer)
End Sub
Private Sub control4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not (KeyAscii >= 48 And KeyAscii <= 57) Then
KeyAscii = 0
End If
End Sub
Private Sub control6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not (KeyAscii >= 48 And KeyAscii <= 57) Then
KeyAscii = 0
End If
End Sub
Private Sub control7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not (KeyAscii >= 48 And KeyAscii <= 57) Then
KeyAscii = 0
End If
End Sub
Private Sub txtPrecioCad_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not (KeyAscii >= 48 And KeyAscii <= 57) Then
KeyAscii = 0
End If
End Sub
Private Sub txtSippingCad_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not (KeyAscii >= 48 And KeyAscii <= 57) Then
KeyAscii = 0
End If
End Sub