Sales Invoice Video#5 Save To Database And Invoice Project Excel VBA
Watch Video
VBA Code
Private Sub BNKBOX_Change()
On Error Resume Next
Me.PAIDBOX = Format(Val(Me.CASHBOX) + Val(Me.BNKBOX), "#####.00")
If Val(Me.BNKBOX) >= 1 Then
Me.CRDBOX.Enabled = True
Else
Me.CRDBOX.Enabled = False
End If
End Sub
Private Sub CASHBOX_Change()
On Error Resume Next
Me.PAIDBOX = Format(Val(Me.CASHBOX) + Val(Me.BNKBOX), "#####.00")
End Sub
Private Sub CommandButton1_Click()
Dim i As Long
For X = 1 To 6
'Textbox Clear
Me("ITEMCODE" & X) = ""
Me("ITEMNAME" & X) = ""
Me("QTYBOX" & X).Value = ""
Me("TAXAMT" & X).Value = ""
Me("RATEBOX" & X).Value = ""
Me("GROSSBOX" & X).Value = ""
Me("TAXBOX" & X) = ""
Me("TOTALBOX" & X).Value = ""
On Error Resume Next
Me("FRM" & X + 1).Visible = False 'frame Hide
Next X
Me.DTPicker1 = Date
Me.NMEBOX = "" 'Textbox Clear
Me.CONTBOX = ""
Me.CASHBOX = ""
Me.BNKBOX = ""
Me.CRDBOX = ""
Me.PAIDBOX = ""
Me.NRBOX = ""
Me.BLBCBOX = ""
Me.TOTALBOX = ""
With Sheet2.Range("A1000000").End(xlUp)
For i = 3 To .Row
If Sheet2.Cells(i, "E") = Val(Me.INVBOX) Then
CNT = CNT + 1
On Error Resume Next
Me("ITEMCODE" & CNT) = Sheet2.Cells(i, "H")
Me("ITEMNAME" & CNT) = Sheet2.Cells(i, "i")
Me("QTYBOX" & CNT).Value = Sheet2.Cells(i, "j")
Me("RATEBOX" & CNT).Value = Sheet2.Cells(i, "K")
Me("GROSSBOX" & CNT).Value = Sheet2.Cells(i, "L")
Me("TAXBOX" & CNT) = Sheet1.Cells(2, "M")
Me("TAXAMT" & CNT).Value = Sheet2.Cells(i, "N")
Me("TOTALBOX" & CNT).Value = Sheet2.Cells(i, "O")
Me.DTPicker1 = Sheet2.Cells(i, "B")
Me.NMEBOX = Sheet2.Cells(i, "F")
Me.CONTBOX = Sheet2.Cells(i, "G")
Me.CASHBOX = Sheet2.Cells(i, "Q")
Me.BNKBOX = Sheet2.Cells(i, "S")
Me.CRDBOX = Sheet2.Cells(i, "U")
Me.PAIDBOX = Sheet2.Cells(i, "V")
Me.NRBOX = Sheet2.Cells(i, "X")
End If
Next i
End With
End Sub
Private Sub Frame2_Click()
End Sub
Private Sub GROSSBOX1_Change()
If Val(Me.GROSSBOX1) >= 1 Then
Me.FRM2.Visible = True
End If
On Error Resume Next
Me.TAXAMT1 = Format(Val(Me.GROSSBOX1) / 100 * Val(Me.TAXBOX1), "#####.00")
Me.TOTALBOX1 = Format(Val(Me.GROSSBOX1) + Val(Me.TAXAMT1), "#####.00")
End Sub
Private Sub GROSSBOX2_Change()
If Val(Me.GROSSBOX2) >= 1 Then
Me.FRM3.Visible = True
End If
Me.TAXAMT2 = Format(Val(Me.GROSSBOX2) / 100 * Val(Me.TAXBOX2), "#####.00")
Me.TOTALBOX2 = Format(Val(Me.GROSSBOX2) + Val(Me.TAXAMT2), "#####.00")
End Sub
Private Sub GROSSBOX3_Change()
If Val(Me.GROSSBOX3) >= 1 Then
Me.FRM4.Visible = True
End If
Me.TAXAMT3 = Format(Val(Me.GROSSBOX3) / 100 * Val(Me.TAXBOX3), "#####.00")
Me.TOTALBOX3 = Format(Val(Me.GROSSBOX3) + Val(Me.TAXAMT3), "#####.00")
End Sub
Private Sub GROSSBOX4_Change()
If Val(Me.GROSSBOX4) >= 1 Then
Me.FRM5.Visible = True
End If
Me.TAXAMT4 = Format(Val(Me.GROSSBOX4) / 100 * Val(Me.TAXBOX4), "#####.00")
Me.TOTALBOX4 = Format(Val(Me.GROSSBOX4) + Val(Me.TAXAMT4), "#####.00")
End Sub
Private Sub GROSSBOX5_Change()
If Val(Me.GROSSBOX5) >= 1 Then
Me.FRM6.Visible = True
End If
Me.TAXAMT5 = Format(Val(Me.GROSSBOX5) / 100 * Val(Me.TAXBOX5), "#####.00")
Me.TOTALBOX5 = Format(Val(Me.GROSSBOX5) + Val(Me.TAXAMT5), "#####.00")
End Sub
Private Sub GROSSBOX6_Change()
Me.TAXAMT6 = Format(Val(Me.GROSSBOX6) / 100 * Val(Me.TAXBOX6), "#####.00")
Me.TOTALBOX6 = Format(Val(Me.GROSSBOX6) + Val(Me.TAXAMT6), "#####.00")
End Sub
Private Sub ITEMCODE1_AfterUpdate()
Dim Mrow As Long
On Error Resume Next
Mrow = Application.WorksheetFunction.Match(Val(Me.ITEMCODE1), Sheet3.Range("B:B"), 0)
Me.ITEMNAME1 = Sheet3.Range("C" & Mrow)
Me.RATEBOX1 = Sheet3.Range("F" & Mrow)
Me.TAXBOX1 = Sheet3.Range("G" & Mrow)
End Sub
Private Sub ITEMCODE1_Change()
End Sub
Private Sub ITEMCODE2_AfterUpdate()
Dim Mrow As Long
On Error Resume Next
Mrow = Application.WorksheetFunction.Match(Val(Me.ITEMCODE2), Sheet3.Range("B:B"), 0)
Me.ITEMNAME2 = Sheet3.Range("C" & Mrow)
Me.RATEBOX2 = Sheet3.Range("F" & Mrow)
Me.TAXBOX2 = Sheet3.Range("G" & Mrow)
End Sub
Private Sub ITEMCODE2_Change()
End Sub
Private Sub ITEMCODE3_AfterUpdate()
Dim Mrow As Long
On Error Resume Next
Mrow = Application.WorksheetFunction.Match(Val(Me.ITEMCODE3), Sheet3.Range("B:B"), 0)
Me.ITEMNAME3 = Sheet3.Range("C" & Mrow)
Me.RATEBOX3 = Sheet3.Range("F" & Mrow)
Me.TAXBOX3 = Sheet3.Range("G" & Mrow)
End Sub
Private Sub ITEMCODE3_Change()
End Sub
Private Sub ITEMCODE4_AfterUpdate()
Dim Mrow As Long
On Error Resume Next
Mrow = Application.WorksheetFunction.Match(Val(Me.ITEMCODE4), Sheet3.Range("B:B"), 0)
Me.ITEMNAME4 = Sheet3.Range("C" & Mrow)
Me.RATEBOX4 = Sheet3.Range("F" & Mrow)
Me.TAXBOX4 = Sheet3.Range("G" & Mrow)
End Sub
Private Sub ITEMCODE4_Change()
End Sub
Private Sub ITEMCODE5_AfterUpdate()
Dim Mrow As Long
On Error Resume Next
Mrow = Application.WorksheetFunction.Match(Val(Me.ITEMCODE5), Sheet3.Range("B:B"), 0)
Me.ITEMNAME5 = Sheet3.Range("C" & Mrow)
Me.RATEBOX5 = Sheet3.Range("F" & Mrow)
Me.TAXBOX5 = Sheet3.Range("G" & Mrow)
End Sub
Private Sub ITEMCODE6_AfterUpdate()
Dim Mrow As Long
On Error Resume Next
Mrow = Application.WorksheetFunction.Match(Val(Me.ITEMCODE6), Sheet3.Range("B:B"), 0)
Me.ITEMNAME6 = Sheet3.Range("C" & Mrow)
Me.RATEBOX6 = Sheet3.Range("F" & Mrow)
Me.TAXBOX6 = Sheet3.Range("G" & Mrow)
End Sub
Private Sub NMEBOX_Change()
On Error Resume Next
Me.NMEBOX = StrConv(Me.NMEBOX, vbProperCase)
End Sub
Private Sub NRBOX_Change()
On Error Resume Next
Me.NRBOX = StrConv(Me.NRBOX, vbProperCase)
End Sub
Private Sub PAIDBOX_Change()
On Error Resume Next
Me.BLBCBOX = Format(Val(Me.TOTALBOX) - Val(Me.PAIDBOX), "#####.00")
End Sub
Private Sub PRINTCMD_Click()
If MsgBox("You want print invoice?", vbYesNo) = vbYes Then
Sheet4.Range("A6:L15").ClearContents
Sheet4.Range("L16:L19").ClearContents
Sheet4.Range("K2") = CDate(Me.DTPicker1)
Sheet4.Range("K3") = Me.INVBOX.Value
Sheet4.Range("C4") = CStr(Me.NMEBOX)
Sheet4.Range("G4") = Me.CONTBOX
For i = 1 To 6
If Me("ITEMNAME" & i) <> "" Then
With Sheet4.Range("A15").End(xlUp)
.Offset(1, 0) = i
.Offset(1, 1) = Me("ITEMNAME" & i)
.Offset(1, 8) = Me("QTYBOX" & i).Value
.Offset(1, 9) = Me("RATEBOX" & i).Value
.Offset(1, 10) = Me("TOTALBOX" & i).Value
End With
Sheet4.Range("L16") = Me.TOTALBOX.Value
Sheet4.Range("L17") = Me.VATBOX.Value
Sheet4.Range("L18") = Me.PAIDBOX.Value
Sheet4.Range("L19") = Me.BLBCBOX.Value
Sheet4.Range("J21") = Me.Adminbox
End If
Next i
With Sheet4.PageSetup
.PrintArea = "A1:L22"
.PaperSize = xlPaperA5
.Orientation = xlLandscape
End With
Sheet4.PrintOut
End If
End Sub
Private Sub QTYBOX1_Change()
On Error Resume Next
Me.GROSSBOX1 = Format(Val(Me.QTYBOX1) * Val(Me.RATEBOX1), "#####.00")
End Sub
Private Sub QTYBOX2_Change()
On Error Resume Next
Me.GROSSBOX2 = Format(Val(Me.QTYBOX2) * Val(Me.RATEBOX2), "#####.00")
End Sub
Private Sub QTYBOX3_Change()
On Error Resume Next
Me.GROSSBOX3 = Format(Val(Me.QTYBOX3) * Val(Me.RATEBOX3), "#####.00")
End Sub
Private Sub QTYBOX4_Change()
On Error Resume Next
Me.GROSSBOX4 = Format(Val(Me.QTYBOX4) * Val(Me.RATEBOX4), "#####.00")
End Sub
Private Sub QTYBOX5_Change()
On Error Resume Next
Me.GROSSBOX5 = Format(Val(Me.QTYBOX5) * Val(Me.RATEBOX5), "#####.00")
End Sub
Private Sub QTYBOX6_Change()
On Error Resume Next
Me.GROSSBOX6 = Format(Val(Me.QTYBOX6) * Val(Me.RATEBOX6), "#####.00")
End Sub
Private Sub RATEBOX1_Change()
On Error Resume Next
Me.GROSSBOX1 = Format(Val(Me.QTYBOX1) * Val(Me.RATEBOX1), "#####.00")
End Sub
Private Sub RATEBOX2_Change()
On Error Resume Next
Me.GROSSBOX2 = Format(Val(Me.QTYBOX2) * Val(Me.RATEBOX2), "#####.00")
End Sub
Private Sub RATEBOX3_Change()
On Error Resume Next
Me.GROSSBOX3 = Format(Val(Me.QTYBOX3) * Val(Me.RATEBOX3), "#####.00")
End Sub
Private Sub RATEBOX4_Change()
On Error Resume Next
Me.GROSSBOX4 = Format(Val(Me.QTYBOX4) * Val(Me.RATEBOX4), "#####.00")
End Sub
Private Sub RATEBOX5_Change()
On Error Resume Next
Me.GROSSBOX5 = Format(Val(Me.QTYBOX5) * Val(Me.RATEBOX5), "#####.00")
End Sub
Private Sub RATEBOX6_Change()
On Error Resume Next
Me.GROSSBOX6 = Format(Val(Me.QTYBOX6) * Val(Me.RATEBOX6), "#####.00")
End Sub
Private Sub REFRESHCMD_Click()
'Userform clear
Dim CTR As Control
For Each CTR In SALESFORM.Controls
On Error Resume Next
CTR.Value = ""
Next CTR
A = Application.WorksheetFunction.CountIfs(Sheets("Database").Range("D:D"), "Sales", _
Sheets("Database").Range("P:P"), ">=" & 1)
Me.INVBOX = A + 1000
For i = 2 To 6
Me("FRM" & i).Visible = False
Next i
End Sub
Private Sub SaveCMD_Click()
Dim CNT As Integer
Dim X As Integer
For X = 1 To 6
With Sheet2.Range("A1000000").End(xlUp)
If Val(Me("ITEMCODE" & X)) >= 1 Then
'To Database(SHEET2)
.Offset(1, 0) = .Row + 100
.Offset(1, 1) = CDate(Me.DTPicker1)
.Offset(1, 2) = Format(Time, "HH:MM AM/PM")
.Offset(1, 3) = "Sales"
.Offset(1, 4) = Me.INVBOX.Value
.Offset(1, 5) = Me.NMEBOX
.Offset(1, 6) = Me.CONTBOX
CNT = CNT + 1
.Offset(1, 7) = Me("ITEMCODE" & CNT)
.Offset(1, 8) = Me("ITEMNAME" & CNT)
.Offset(1, 9) = Me("QTYBOX" & CNT).Value
.Offset(1, 10) = Me("RATEBOX" & CNT).Value
.Offset(1, 11) = Me("GROSSBOX" & CNT).Value
.Offset(1, 12) = Me("TAXBOX" & CNT)
.Offset(1, 13) = Me("TAXAMT" & CNT).Value
.Offset(1, 14) = Me("TOTALBOX" & CNT).Value
End If
End With
Next X
'To Database(SHEET2)
With Sheet2.Range("A1000000").End(xlUp)
.Offset(1, 0) = .Row + 100
.Offset(1, 1) = CDate(Me.DTPicker1)
.Offset(1, 2) = Format(Time, "HH:MM AM/PM")
.Offset(1, 3) = "Sales"
.Offset(1, 4) = Me.INVBOX.Value
.Offset(1, 5) = Me.NMEBOX
.Offset(1, 6) = Me.CONTBOX
.Offset(1, 15) = Me.TOTALBOX.Value
.Offset(1, 16) = Me.CASHBOX.Value
.Offset(1, 18) = Me.BNKBOX.Value
.Offset(1, 20) = Me.CRDBOX
.Offset(1, 21) = Me.PAIDBOX.Value
.Offset(1, 22) = Me.BLBCBOX.Value
.Offset(1, 23) = Me.NRBOX.Value
.Offset(1, 24) = Me.Adminbox
End With
Call PRINTCMD_Click
Call REFRESHCMD_Click
End Sub
Private Sub SpinButton1_SpinDown()
On Error Resume Next
Me.INVBOX = Val(Me.INVBOX) - 1
Call CommandButton1_Click
End Sub
Private Sub SpinButton1_SpinUp()
On Error Resume Next
Me.INVBOX = Val(Me.INVBOX) + 1
Call CommandButton1_Click
End Sub
Private Sub TAXBOX1_Change()
On Error Resume Next
Me.TAXAMT1 = Format(Val(Me.GROSSBOX1) / 100 * Val(Me.TAXBOX1), "#####.00")
Me.TOTALBOX1 = Format(Val(Me.GROSSBOX1) + Val(Me.TAXAMT1), "#####.00")
End Sub
Private Sub TAXBOX2_Change()
On Error Resume Next
Me.TAXAMT2 = Format(Val(Me.GROSSBOX2) / 100 * Val(Me.TAXBOX2), "#####.00")
Me.TOTALBOX2 = Format(Val(Me.GROSSBOX2) + Val(Me.TAXAMT2), "#####.00")
End Sub
Private Sub TAXBOX3_Change()
On Error Resume Next
Me.TAXAMT3 = Format(Val(Me.GROSSBOX3) / 100 * Val(Me.TAXBOX3), "#####.00")
Me.TOTALBOX3 = Format(Val(Me.GROSSBOX3) + Val(Me.TAXAMT3), "#####.00")
End Sub
Private Sub TAXBOX4_Change()
On Error Resume Next
Me.TAXAMT4 = Format(Val(Me.GROSSBOX4) / 100 * Val(Me.TAXBOX4), "#####.00")
Me.TOTALBOX4 = Format(Val(Me.GROSSBOX4) + Val(Me.TAXAMT4), "#####.00")
End Sub
Private Sub TAXBOX5_Change()
On Error Resume Next
Me.TAXAMT5 = Format(Val(Me.GROSSBOX5) / 100 * Val(Me.TAXBOX5), "#####.00")
Me.TOTALBOX5 = Format(Val(Me.GROSSBOX5) + Val(Me.TAXAMT5), "#####.00")
End Sub
Private Sub TAXBOX6_Change()
On Error Resume Next
Me.TAXAMT6 = Format(Val(Me.GROSSBOX6) / 100 * Val(Me.TAXBOX6), "#####.00")
Me.TOTALBOX6 = Format(Val(Me.GROSSBOX6) + Val(Me.TAXAMT6), "#####.00")
End Sub
Private Sub TOTALBOX_Change()
On Error Resume Next
Me.BLBCBOX = Format(Val(Me.TOTALBOX) - Val(Me.PAIDBOX), "#####.00")
End Sub
Private Sub TOTALBOX1_Change()
Dim X As Integer
Dim SUM, SUM1 As Double
For X = 1 To 6
SUM = SUM + Val(Me("TOTALBOX" & X))
SUM1 = SUM1 + Val(Me("TAXAMT" & X))
Next X
Me.TOTALBOX = Format(SUM, "#####.00")
Me.VATBOX = Format(SUM1, "#####.00")
End Sub
Private Sub TOTALBOX2_Change()
Dim X As Integer
Dim SUM, SUM1 As Double
For X = 1 To 6
SUM = SUM + Val(Me("TOTALBOX" & X))
SUM1 = SUM1 + Val(Me("TAXAMT" & X))
Next X
Me.TOTALBOX = Format(SUM, "#####.00")
Me.VATBOX = Format(SUM1, "#####.00")
End Sub
Private Sub TOTALBOX3_Change()
Dim X As Integer
Dim SUM, SUM1 As Double
For X = 1 To 6
SUM = SUM + Val(Me("TOTALBOX" & X))
SUM1 = SUM1 + Val(Me("TAXAMT" & X))
Next X
Me.TOTALBOX = Format(SUM, "#####.00")
Me.VATBOX = Format(SUM1, "#####.00")
End Sub
Private Sub TOTALBOX4_Change()
Dim X As Integer
Dim SUM, SUM1 As Double
For X = 1 To 6
SUM = SUM + Val(Me("TOTALBOX" & X))
SUM1 = SUM1 + Val(Me("TAXAMT" & X))
Next X
Me.TOTALBOX = Format(SUM, "#####.00")
Me.VATBOX = Format(SUM1, "#####.00")
End Sub
Private Sub TOTALBOX5_Change()
Dim X As Integer
Dim SUM, SUM1 As Double
For X = 1 To 6
SUM = SUM + Val(Me("TOTALBOX" & X))
SUM1 = SUM1 + Val(Me("TAXAMT" & X))
Next X
Me.TOTALBOX = Format(SUM, "#####.00")
Me.VATBOX = Format(SUM1, "#####.00")
End Sub
Private Sub TOTALBOX6_Change()
Dim X As Integer
Dim SUM, SUM1 As Double
For X = 1 To 6
SUM = SUM + Val(Me("TOTALBOX" & X))
SUM1 = SUM1 + Val(Me("TAXAMT" & X))
Next X
Me.TOTALBOX = Format(SUM, "#####.00")
Me.VATBOX = Format(SUM1, "#####.00")
End Sub
Private Sub UserForm_Activate()
Dim A As Long
On Error Resume Next
A = Application.WorksheetFunction.CountIfs(Sheets("Database").Range("D:D"), _
Sales, Sheets("Database").Range("P:P"), ">=" & 1)
Me.INVBOX = A + 1000
Me.Adminbox.AddItem "Cashier 1"
Me.Adminbox.AddItem "Cashier 2"
Me.Adminbox.AddItem "Cashier 3"
Me.Adminbox.AddItem "Cashier 4"
Me.Adminbox = "Cashier 1"
End Sub
Private Sub UserForm_Initialize()
Dim FWidth As Long
FWidth = Application.Width
Me.Width = FWidth - 20
Me.Frame1.Width = Me.Width - 20
Me.Frame2.Width = Me.Width - 20
Me.Label1.Width = Me.Frame1.Width
Me.DTPicker1 = Date
Lwidth = Me.Frame1.Width
Me.FRM1.Width = Lwidth
Me.FRM2.Width = Lwidth
Me.FRM3.Width = Lwidth
Me.FRM4.Width = Lwidth
Me.FRM5.Width = Lwidth
Me.FRM6.Width = Lwidth
Me.Label3.Width = Me.Frame1.Width
Me.FRM1.SetFocus
End Sub
