VBA Code:
Option Explicit
Function Validate() As Boolean
Dim frm As Worksheet
Set frm = ThisWorkbook.Sheets("Form")
Validate = True
With frm
.Range("I6").Interior.Color = xlNone
.Range("I8").Interior.Color = xlNone
.Range("I10").Interior.Color = xlNone
.Range("I12").Interior.Color = xlNone
End With
'Fish Type
If Trim(frm.Range("I6").Value) <> "Whiting" And Trim(frm.Range("I6").Value) <> "Bottom Fish" And Trim(frm.Range("I6").Value) <> "Crab" Then
MsgBox "Please Select Fish Type.", vbOKOnly + vbInformation, "Fish Type"
frm.Range("I6").Select
frm.Range("I6").Interior.Color = vbRed
Validate = False
Exit Function
End If
'Finished Pounds
If Trim(frm.Range("I8").Value) <> "" Or Not IsNumeric(Trim(frm.Range("I8").Value)) Then
MsgBox "Finished Pounds is blank.", vbOKOnly + vbInformation, "Finished Pounds"
frm.Range("I8").Select
frm.Range("I8").Interior.Color = vbRed
Validate = False
Exit Function
End If
'Sales Per Pound
If Trim(frm.Range("I10").Value) <> "" Or Not IsNumeric(Trim(frm.Range("I10").Value)) Then
MsgBox "Sales Per Pound is Blank.", vbOKOnly + vbInformation, "Sales Per Pound"
frm.Range("I10").Select
frm.Range("I10").Interior.Color = vbRed
Validate = False
Exit Function
End If
'Cost Per Pound
If Trim(frm.Range("I12").Value) <> "" Or Not IsNumeric(Trim(frm.Range("I12").Value)) Then
MsgBox "Cost Per Pound is Blank.", vbOKOnly + vbInformation, "Cost Per Pound"
frm.Range("I12").Select
frm.Range("I12").Interior.Color = vbRed
Validate = False
Exit Function
End If
End Function
Sub Reset()
With Sheets("form")
.Range("I6").Interior.Color = xlNone
.Range("I6").Value = ""
.Range("I8").Interior.Color = xlNone
.Range("I8").Value = ""
.Range("I10").Interior.Color = xlNone
.Range("I10").Value = ""
.Range("I12").Interior.Color = xlNone
.Range("I12").Value = ""
End With
End Sub
Sub Save()
Dim frm As Worksheet
Dim data As Worksheet
Dim iRow As Long
Dim iSerial As Long
Set frm = ThisWorkbook.Sheets("Form")
Set data = ThisWorkbook.Sheets("Data")
If Trim(frm.Range("M1").Value) = "" Then
iRow = data.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
iSerial = data.Cells(iRow - 1, 1).Value + 1
Else
iRow = frm.Range("L1").Value
iSerial = frm.Range("M1").Value
End If
With data
.Cells(iRow, 1).Value = iSerial
.Cells(iRow, 2).Value = frm.Range("I6").Value
.Cells(iRow, 3).Value = frm.Range("I8").Value
.Cells(iRow, 4).Value = frm.Range("I10").Value
.Cells(iRow, 5).Value = frm.Range("I12").Value
.Cells(iRow, 6).Value = Application.UserName
.Cells(iRow, 7).Value = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]
End With
frm.Range("L1").Value = ""
frm.Range("M1").Value = ""
End Sub
Sub Modify()
Dim iRow As Long
Dim iSerial As Long
iSerial = Application.inoutbox("please enter Fish type to make Modification.", "modify", , , , , 1)
On Error Resume Next
iRow = Application.WorksheetFunction(Application.WorksheetFunction.Match(iSerial, Sheets("data").Range("A:A"), 0))
On Error GoTo 0
If iRow = 0 Then
MsgBox "No record is found.", vbOKOnly + vbCritical, "No record"
Exit Sub
End If
Sheets("Form").Range("L1").Value = iRow
Sheets("Form").Range("M1").Value = iSerial
Sheets("form").Range("I6").Value = Sheets("data").Cells(iRow, 2).Value
Sheets("form").Range("I8").Value = Sheets("data").Cells(iRow, 3).Value
Sheets("form").Range("I10").Value = Sheets("data").Cells(iRow, 4).Value
Sheets("form").Range("I12").Value = Sheets("data").Cells(iRow, 5).Value
End Sub
Sub Delete()
Dim iRow As Long
Dim iSerial As Long
iSerial = Application.InputBox("Please Enter Fish Type to delete record.", "Delete", , , , , , 1)
On Error Resume Next
iRow = Application.WorksheetFunction(Application.WorksheetFunction.Match(iSerial, Sheets("data").Range("A:A"), 0))
On Error GoTo 0
If iRow = 0 Then
MsgBox "No record found.", vbOKOnly + vbCriticial, "No Record"
Exit Sub
End If
Sheets("data").Cells(iRow, 1).EntireRow.Delete shift:=xlUp
End Sub
