Option Explicit
Dim GettingDate As Boolean
Dim TotalPaidOut As Double
Dim CashInTill As Double
Dim Change As Double
Dim TillArray(11) As Double
Dim FloatArray(4) As Double
'****************************************************************************
'
' Buttons
'
'****************************************************************************
Private Sub cmdPrint_Click()
' Need to write
End Sub
'****************************************************************************
Private Sub cmdSave_Click()
SaveThisFile
End Sub
'****************************************************************************
Public Sub cmdDone_Click()
AllDone
End Sub
'****************************************************************************
'
' Label & Text Box Exit routines
'
'****************************************************************************
Private Sub txtStaff_Exit(ByVal Cancel As MSForms.ReturnBoolean)
FormFile.Sheets("Cashup").Range("Staff").Cells(1, 1).Value = Me("txtStaff").Value
End Sub
'****************************************************************************
'
' Paid Out
'
'****************************************************************************
Private Sub txtItem1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
PaidOutItemExit (1)
End Sub
'****************************************************************************
Private Sub txtItem2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
PaidOutItemExit (2)
End Sub
Private Sub txtItem3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
PaidOutItemExit (3)
End Sub
Private Sub txtItem4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
PaidOutItemExit (4)
End Sub
Private Sub txtItem5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
PaidOutItemExit (5)
End Sub
Sub PaidOutItemExit(TheRow As Integer)
FormFile.Sheets("Cashup").Range("PaidOut").Cells(TheRow, 1).Value = Me("txtItem" & TheRow).Value
End Sub
Private Sub txtValueItem1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = PaidOutValueExit(1)
End Sub
Private Sub txtValueItem2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = PaidOutValueExit(2)
End Sub
Private Sub txtValueItem3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = PaidOutValueExit(3)
End Sub
Private Sub txtValueItem4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = PaidOutValueExit(4)
End Sub
Private Sub txtValueItem5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = PaidOutValueExit(5)
End Sub
Function PaidOutValueExit(TheRow As Integer) As Boolean
PaidOutValueExit = False
If Me("txtValueItem" & TheRow).Value = "" Then
If Me("txtItem" & TheRow).Value <> "" Then
MsgBox "You must enter a valid number."
PaidOutValueExit = True
End If
Exit Function
End If
If Not IsNumeric(Me("txtValueItem" & TheRow).Value) Then
MsgBox ("Entry must be a number.")
PaidOutValueExit = True
Else
Me("txtValueItem" & TheRow).Value = Format(Me("txtValueItem" & TheRow).Value, "Fixed")
FormFile.Sheets("Cashup").Range("PaidOut").Cells(TheRow, 2).Value = Me("txtValueItem" & TheRow).Value
ProcessForm
End If
End Function
'****************************************************************************
'
' Till Count
'
'****************************************************************************
Private Sub txtTillCount1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TillCountExit(1)
End Sub
'****************************************************************************
Private Sub txtTillCount2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TillCountExit(2)
End Sub
Private Sub txtTillCount3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TillCountExit(3)
End Sub
Private Sub txtTillCount4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TillCountExit(4)
End Sub
Private Sub txtTillCount5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TillCountExit(5)
End Sub
Private Sub txtTillCount6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TillCountExit(6)
End Sub
Private Sub txtTillCount7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TillCountExit(7)
End Sub
Private Sub txtTillCount8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TillCountExit(8)
End Sub
Private Sub txtTillCount9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TillCountExit(9)
End Sub
Private Sub txtTillCount10_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TillCountExit(10)
End Sub
Private Sub txtTillCount11_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TillCountExit(11)
End Sub
Function TillCountExit(TheRow As Integer) As Boolean
Dim tmpVal As Double
TillCountExit = False
If Me("txtTillCount" & TheRow).Text = "" Then Me("txtTillCount" & TheRow).Text = 0
If Not IsNumeric(Me("txtTillCount" & TheRow).Value) Then
MsgBox "The value you have entered is not a valid number."
TillCountExit = True
Exit Function
End If
If Not WholeNum(Me("txtTillCount" & TheRow).Value) Then
MsgBox "You must enter a valid number."
TillCountExit = True
Else
FormFile.Sheets("Cashup").Range("TillCount").Cells(TheRow, 1).Value = Me("txtTillCount" & TheRow).Value
tmpVal = CInt(Me("txtTillCount" & TheRow).Value) * TillArray(TheRow - 1)
Me("lblTillValue" & TheRow).Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("TillCount").Cells(TheRow, 2).Value = tmpVal
ProcessForm
End If
End Function
'****************************************************************************
'
' Float
'
'****************************************************************************
Private Sub txtNumFloat1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = NumFloatExit(1)
End Sub
Private Sub txtNumFloat2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = NumFloatExit(2)
End Sub
Private Sub txtNumFloat3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = NumFloatExit(3)
End Sub
Private Sub txtNumFloat4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = NumFloatExit(4)
End Sub
Function NumFloatExit(TheRow As Integer) As Boolean
Dim tmpVal As Double
NumFloatExit = False
If Me("txtNumFloat" & TheRow).Text = "" Then Me("txtNumFloat" & TheRow).Text = 0
If Not IsNumeric(Me("txtNumFloat" & TheRow).Value) Then
MsgBox "The value you have entered is not a valid number."
NumFloatExit = True
Exit Function
End If
If Not WholeNum(Me("txtNumFloat" & TheRow).Value) Then
MsgBox "You must enter a valid number."
NumFloatExit = True
Else
FormFile.Sheets("Cashup").Range("Float").Cells(TheRow, 2).Value = Me("txtNumfloat" & TheRow).Value
tmpVal = CInt(Me("txtNumFloat" & TheRow).Value) * FloatArray(TheRow - 1)
Me("lblValueFloat" & TheRow).Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("Float").Cells(TheRow, 3).Value = tmpVal
ProcessForm
End If
End Function
'****************************************************************************
'
' Others
'
'****************************************************************************
Private Sub txtFloatIn_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me("txtFloatIn").Text = "" Then Me("txtFloatIn").Text = 0
If Not IsNumeric(Me("txtFloatIn").Value) Then
MsgBox "You must enter a valid number."
Cancel = True
Else
Me("txtFloatIn").Text = Format(Me("txtFloatIn").Value, "Fixed")
FormFile.Sheets("Cashup").Range("FloatIn").Cells(1, 1).Value = Me("txtFloatIn").Value
End If
ProcessForm
End Sub
Private Sub txtCashSalesPOS_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me("txtCashSalesPOS").Text = "" Then Me("txtCashSalesPOS").Text = 0
If Not IsNumeric(Me("txtCashSalesPOS").Value) Then
MsgBox "You must enter a valid number."
Cancel = True
Else
Me("txtCashSalesPOS").Text = Format(Me("txtCashSalesPOS").Value, "Fixed")
FormFile.Sheets("Cashup").Range("CashPOS").Cells(1, 1).Value = Me("txtCashSalesPOS").Value
End If
ProcessForm
End Sub
Private Sub txtCCMachine_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me("txtCCMachine").Text = "" Then Me("txtCCMachine").Text = 0
If Not IsNumeric(Me("txtCCMachine").Value) Then
MsgBox "You must enter a valid number."
Cancel = True
Else
Me("txtCCMachine").Text = Format(Me("txtCCMachine").Value, "Fixed")
FormFile.Sheets("Cashup").Range("CCMachine").Cells(1, 1).Value = Me("txtCCMachine").Value
End If
ProcessForm
End Sub
'****************************************************************************
Private Sub txtCCPOS_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me("txtCCPOS").Text = "" Then Me("txtCCPOS").Text = 0
If Not IsNumeric(Me("txtCCPOS").Value) Then
MsgBox "You must enter a valid number."
Cancel = True
Else
Me("txtCCPOS").Text = Format(Me("txtCCPOS").Value, "Fixed")
FormFile.Sheets("Cashup").Range("CCPOS").Cells(1, 1).Value = Me("txtCCPOS").Value
End If
ProcessForm
End Sub
'****************************************************************************
'
' Process the form
'
'****************************************************************************
Sub ProcessForm()
Dim tmpVal As Double
Dim x As Integer
' Paid Out
tmpVal = 0
For x = 1 To 5
If Me("txtValueItem" & x).Value <> "" Then
tmpVal = tmpVal + CDbl(Me("txtValueItem" & x).Value)
End If
Next
Me("lblCashPaidOut").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("CashPaidOut").Cells(1, 1).Value = tmpVal
Me("lblPlusPaidOut").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("PlusPaidOut").Cells(1, 1).Value = tmpVal
' Till Count
tmpVal = 0
For x = 11 To 1 Step -1
If Me("lblTillValue" & x).Caption <> "" Then
tmpVal = tmpVal + CDbl(Me("lblTillValue" & x).Caption)
If x = 6 Then
Change = tmpVal
End If
End If
Next
Me("lblCashInTill").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("CashInTill").Cells(1, 1).Value = tmpVal
Me("lblChange").Caption = Format(Change, "Fixed")
FormFile.Sheets("Cashup").Range("Change").Cells(1, 1).Value = Change
' Cash Over/Short
tmpVal = 0
If Me("lblCashInTill").Caption <> "" Then
tmpVal = CDbl(Me("lblCashInTill").Caption)
End If
If Me("txtFloatIn").Value <> "" Then
tmpVal = tmpVal - CDbl(Me("txtFloatIn").Value)
End If
If Me("lblPlusPaidOut").Caption <> "" Then
tmpVal = tmpVal - CDbl(Me("lblPlusPaidOut").Caption)
End If
Me("lblActualCash").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("ActualCash").Cells(1, 1).Value = tmpVal
tmpVal = 0
If Me("txtCashSalesPOS").Value <> "" And Me("lblActualCash").Caption <> "" Then
tmpVal = CDbl(Me("txtCashSalesPOS").Value) - CDbl(Me("lblActualCash").Caption)
If tmpVal >= 0 Then
Me("lblCashOverShort").Caption = Format(tmpVal, "Fixed")
Me("lblCashOverShort").ForeColor = &H80000012
Else
Me("lblCashOverShort").Caption = "(" & Format(tmpVal, "Fixed") & ")"
Me("lblCashOverShort").ForeColor = &HFF
End If
FormFile.Sheets("Cashup").Range("CashOverShort").Cells(1, 1).Value = tmpVal
End If
' Card Over/Short
tmpVal = 0
If Me("txtCCMachine").Value <> "" And Me("txtCCPOS").Value <> "" Then
tmpVal = CDbl(Me("txtCCMachine").Value) - CDbl(Me("txtCCPOS").Value)
If tmpVal >= 0 Then
Me("lblCCOverShort").Caption = Format(tmpVal, "Fixed")
Me("lblCCOverShort").ForeColor = &H80000012
Else
Me("lblCCOverShort").Caption = "(" & Format(tmpVal, "Fixed") & ")"
Me("lblCCOverShort").ForeColor = &HFF
End If
FormFile.Sheets("Cashup").Range("CCOverShort").Cells(1, 1).Value = tmpVal
End If
' Float
tmpVal = 0
For x = 1 To 4
If Me("lblValueFloat" & x).Caption <> "" Then
tmpVal = tmpVal + CDbl(Me("lblValueFloat" & x).Caption)
End If
Next
tmpVal = tmpVal + Change
Me("lblFloatOut").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("FloatOut").Cells(1, 1).Value = tmpVal
' Summary
If Me("txtCashSalesPOS").Value <> "" Then
Me("lblSummaryCash").Caption = Format(Me("txtCashSalesPOS").Value, "Fixed")
FormFile.Sheets("Cashup").Range("SummaryCash").Cells(1, 1).Value = tmpVal
End If
If Me("txtCCMachine").Value <> "" Then
Me("lblSummaryCard").Caption = Format(Me("txtCCMachine").Value, "Fixed")
FormFile.Sheets("Cashup").Range("SummaryCard").Cells(1, 1).Value = tmpVal
End If
If Me("lblSummaryCash").Caption <> "" And Me("lblSummaryCard").Caption <> "" Then
tmpVal = CDbl(Me("lblSummaryCash").Caption) + CDbl(Me("lblSummaryCard").Caption)
Me("lblTurnover").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("Turnover").Cells(1, 1).Value = tmpVal
End If
' Cash to Bank
If Me("lblActualCash").Caption <> "" And Me("lblFloatOut").Caption <> "" Then
tmpVal = CDbl(Me("lblActualCash").Caption) - CDbl(Me("lblFloatOut").Caption)
Me("lblCashToBank").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("CashToBank").Cells(1, 1).Value = tmpVal
End If
End Sub
'****************************************************************************
'
' Init
'
'****************************************************************************
Private Sub UserForm_Activate()
Dim x As Integer
TillArray(0) = 200
TillArray(1) = 100
TillArray(2) = 50
TillArray(3) = 20
TillArray(4) = 10
TillArray(5) = 5
TillArray(6) = 2
TillArray(7) = 1
TillArray(9) = 0.5
TillArray(9) = 0.2
TillArray(10) = 0.1
FloatArray(0) = 100
FloatArray(1) = 50
FloatArray(2) = 20
FloatArray(3) = 10
Me("lblDate").Caption = DisplayDate
GetYesterday
If EditThisDate Then
PopulateForm
Else
' Get the standard float always
For x = 1 To 4
Me("lblStandard" & x).Caption = Format(FormFile.Sheets("Cashup").Range("Float").Cells(x, 1).Value, "#0")
Next
End If
End Sub
'****************************************************************************
Sub PopulateForm()
Dim x As Integer
Me("lblDate").Caption = FormFile.Sheets("Cashup").Range("Date").Cells(1, 1).Value
Me("txtStaff").Text = FormFile.Sheets("Cashup").Range("Staff").Cells(1, 1).Value
For x = 1 To 5
Me("txtItem" & x).Text = FormFile.Sheets("Cashup").Range("PaidOut").Cells(x, 1).Value
Me("txtValueItem" & x).Text = Format(FormFile.Sheets("Cashup").Range("PaidOut").Cells(x, 2).Value, "Fixed")
Next
Me("lblCashPaidOut").Caption = Format(FormFile.Sheets("Cashup").Range("CashPaidOut").Cells(1, 1).Value, "Fixed")
For x = 1 To 11
Me("txtTillCount" & x).Text = Format(FormFile.Sheets("Cashup").Range("TillCount").Cells(x, 1).Value, "#0")
Me("lblTillValue" & x).Caption = Format(FormFile.Sheets("Cashup").Range("TillCount").Cells(x, 2).Value, "Fixed")
Next
Me("lblCashInTill").Caption = Format(FormFile.Sheets("Cashup").Range("CashInTill").Cells(1, 1).Value, "Fixed")
Me("txtFloatIn").Text = Format(FormFile.Sheets("Cashup").Range("FloatIn").Cells(1, 1).Value, "Fixed")
Me("lblPlusPaidOut").Caption = Format(FormFile.Sheets("Cashup").Range("PlusPaidOut").Cells(1, 1).Value, "Fixed")
Me("lblActualCash").Caption = Format(FormFile.Sheets("Cashup").Range("ActualCash").Cells(1, 1).Value, "Fixed")
Me("txtCashSalesPOS").Text = Format(FormFile.Sheets("Cashup").Range("CashPOS").Cells(1, 1).Value, "Fixed")
Me("lblCashOverShort").Caption = Format(FormFile.Sheets("Cashup").Range("CashOverShort").Cells(1, 1).Value, "Fixed")
Me("txtCCMachine").Text = Format(FormFile.Sheets("Cashup").Range("CCMachine").Cells(1, 1).Value, "Fixed")
Me("txtCCPOS").Text = Format(FormFile.Sheets("Cashup").Range("CCPOS").Cells(1, 1).Value, "Fixed")
Me("lblCCOverShort").Caption = Format(FormFile.Sheets("Cashup").Range("CCOverShort").Cells(1, 1).Value, "Fixed")
For x = 1 To 4
Me("lblStandard" & x).Caption = Format(FormFile.Sheets("Cashup").Range("Float").Cells(x, 1).Value, "#0")
Me("txtNumFloat" & x).Text = Format(FormFile.Sheets("Cashup").Range("Float").Cells(x, 2).Value, "#0")
Me("lblValueFloat" & x).Caption = Format(FormFile.Sheets("Cashup").Range("Float").Cells(x, 3).Value, "Fixed")
Next
Me("lblChange").Caption = Format(FormFile.Sheets("Cashup").Range("Change").Cells(1, 1).Value, "Fixed")
Me("lblFloatOut").Caption = Format(FormFile.Sheets("Cashup").Range("FloatOut").Cells(1, 1).Value, "Fixed")
Me("lblSummaryCash").Caption = Format(FormFile.Sheets("Cashup").Range("SummaryCash").Cells(1, 1).Value, "Fixed")
Me("lblSummaryCard").Caption = Format(FormFile.Sheets("Cashup").Range("SummaryCard").Cells(1, 1).Value, "Fixed")
Me("lblTurnover").Caption = Format(FormFile.Sheets("Cashup").Range("Turnover").Cells(1, 1).Value, "Fixed")
Me("lblCashToBank").Caption = Format(FormFile.Sheets("Cashup").Range("CashToBank").Cells(1, 1).Value, "Fixed")
' Just to be on the safe side, process till count and float count
For x = 1 To 11
TillCountExit (x)
Next
For x = 1 To 4
NumFloatExit (x)
Next
ProcessForm
End Sub
And in the main module