Monday, September 13, 2021

How To Use Table In Excel VBA

Sales Invoice Video#6 Update And delete Invoice Excel VBA

Combobox Track Column And Listindex Excel VBA

How to Create Hyperlink Combobox Userform Excel VBA

How Create Slide Menu Bar In Userform Excvel VBA

Listbox Running Balance for Every ledgers Excel VBA

Entry Form with require And Not Require Excel VBA

Entry Form with require And Not Require Excel VBA

Listbox fill By Custom Choose Transaction Userform Excel VBA

Combobox List Auto Upadate Userform Excel VBA

Monday, May 24, 2021

Listbox Data Transfer To Particular Sheet Excel VBA

 Listbox Data Transfer To Particular Sheet Excel VBA

Listbox Data Transfer To Particular Sheet Excel VBA


Watch Video

VBA Code

Private Sub ComboBox1_Change() 'Combobox
For c = 1 To Me.ListBox1.ListCount - 1
If Format(Me.ListBox1.List(c, 0), "M") = Val(Me.ComboBox1.ListIndex) + 1 Then
Me.ListBox1.Selected(c) = True
End If
Next c
Me.CommandButton2.Enabled = True
End Sub

Private Sub CommandButton2_Click()  'Custom Transfer
Dim i As Long, MNT As Integer
Application.ScreenUpdating = False
For i = 1 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True Then
MNT = Format(Me.ListBox1.List(i), "M")
Sheets(MNT + 1).Range("A1000000").End(xlUp).Offset(1, 0) = Me.ListBox1.List(i, 0)
Sheets(MNT + 1).Range("A1000000").End(xlUp).Offset(0, 1) = Me.ListBox1.List(i, 1)
Sheets(MNT + 1).Range("A1000000").End(xlUp).Offset(0, 2) = Me.ListBox1.List(i, 2)
Sheets(MNT + 1).Range("A1000000").End(xlUp).Offset(0, 3) = Me.ListBox1.List(i, 3)
End If
Me.ListBox1.Selected(i) = False
Next i
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton3_Click()  'Transfer All
Dim i As Long, MNT As Integer
Application.ScreenUpdating = False
For i = 1 To Me.ListBox1.ListCount - 1
MNT = Format(Me.ListBox1.List(i), "M") + 1
With Sheets(MNT).Range("A1000000").End(xlUp)
On Error Resume Next
.Offset(1, 0) = Me.ListBox1.List(i, 0)
.Offset(1, 1) = Me.ListBox1.List(i, 1)
.Offset(1, 2) = Me.ListBox1.List(i, 2)
.Offset(1, 3) = Me.ListBox1.List(i, 3)
End With
Next i
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton4_Click()   'Unselect
Dim i As Integer
For i = 1 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = False
Next i
Me.CommandButton2.Enabled = False
End Sub
Private Sub ListBox1_Enter()
Me.CommandButton2.Enabled = True
End Sub

Private Sub UserForm_Initialize() 'Listbox Fill
Dim i As Long
For i = 1 To Sheet1.Range("A1000000").End(xlUp).Row
Me.ListBox1.AddItem
For c = 1 To 4
On Error Resume Next
Me.ListBox1.List(i - 1, c - 1) = Sheet1.Cells(i, c)
Next c
Next i
Me.ListBox1.Selected(0) = True

For x = 1 To Worksheets.Count   'Combobox Fill
Me.ComboBox1.AddItem MonthName(x)
Next x
End Sub

Private Sub CommandButton2_Click()   'Clear Worksheet
Dim i As Long, MNT As Integer
Application.ScreenUpdating = False
For MNT = 2 To Worksheets.Count
For i = 2 To Sheets(MNT).Range("A1000000").End(xlUp).Row
Sheets(MNT).Range("A" & 2, "D" & i).ClearContents
Next i
Next MNT
Application.ScreenUpdating = True
End Sub






Thursday, May 13, 2021

 Listview Conditional format And Text Alignment Excel VBA

Watch Video



Monday, February 22, 2021

Create Hide And Seek Contact form Excel VBA

 Create Hide And Seek Contact form Excel VBA

Create Hide And Seek Contact form Excel VBA


Watch Video


VBA Code

Private Sub CMD1_Click()
On Error Resume Next
a = 24
b = 30
Me.TEL2.Left = a
Me.TEL2.Top = Me.TEL1.Top + b
Me.MOBILE1.Top = Me.MOBILE1.Top + b
Me.MOBILE2.Top = Me.MOBILE1.Top + b
Me.EMAIL1.Top = Me.EMAIL1.Top + b
Me.EMAIL2.Top = Me.EMAIL1.Top + b
Me.ADDRESS1.Top = Me.ADDRESS1.Top + b
Me.ADDRESS2.Top = Me.ADDRESS1.Top + b
Me.CMD2.Top = Me.MOBILE1.Top
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD6.Top = Me.MOBILE1.Top
Me.CMD7.Top = Me.EMAIL1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.TEL2.TabStop = True
Me.CMD1.Visible = False
Me.CMD5.Visible = True
End Sub

Private Sub CMD2_Click()
On Error Resume Next
a = 24
b = 30
Me.MOBILE2.Left = a
Me.MOBILE2.Top = Me.MOBILE1.Top + 30
Me.EMAIL1.Top = Me.EMAIL1.Top + b
Me.EMAIL2.Top = Me.EMAIL1.Top + b
Me.ADDRESS1.Top = Me.ADDRESS1.Top + b
Me.ADDRESS2.Top = Me.ADDRESS1.Top + b
Me.CMD2.Top = Me.MOBILE1.Top
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD6.Top = Me.MOBILE1.Top
Me.CMD7.Top = Me.EMAIL1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.MOBILE2.TabStop = True
Me.CMD2.Visible = False
Me.CMD6.Visible = True
End Sub

Private Sub CMD3_Click()
On Error Resume Next
a = 24
b = 30
Me.EMAIL2.Left = a
Me.EMAIL2.Top = Me.EMAIL1.Top + 30
Me.ADDRESS1.Top = Me.ADDRESS1.Top + b
Me.ADDRESS2.Top = Me.ADDRESS1.Top + b
Me.CMD2.Top = Me.MOBILE1.Top
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD6.Top = Me.MOBILE1.Top
Me.CMD7.Top = Me.EMAIL1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.EMAIL2.TabStop = True
Me.CMD3.Visible = False
Me.CMD7.Visible = True
End Sub

Private Sub CMD4_Click()
On Error Resume Next
a = 24
b = 30
Me.ADDRESS2.Left = a
Me.ADDRESS2.Top = Me.ADDRESS1.Top + b
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.ADDRESS2.TabStop = True
Me.CMD4.Visible = False
Me.CMD8.Visible = True
End Sub

Private Sub CMD5_Click()
On Error Resume Next
a = 465.75
b = 30
Me.TEL2.Left = a
Me.TEL2.Top = Me.TEL1.Top - b
Me.MOBILE1.Top = Me.MOBILE1.Top - 30
Me.MOBILE2.Top = Me.MOBILE1.Top - 30
Me.EMAIL1.Top = Me.EMAIL1.Top - b
Me.EMAIL2.Top = Me.EMAIL1.Top - b

Me.ADDRESS1.Top = Me.ADDRESS1.Top - b
Me.ADDRESS2.Top = Me.ADDRESS1.Top - b
Me.CMD2.Top = Me.MOBILE1.Top
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD6.Top = Me.MOBILE1.Top
Me.CMD7.Top = Me.EMAIL1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.CMD1.Visible = True
Me.CMD5.Visible = False
End Sub

Private Sub CMD6_Click()
On Error Resume Next
a = 465.75
b = 30
Me.MOBILE2.Left = a
Me.MOBILE2.Top = Me.MOBILE1.Top - 30
Me.EMAIL1.Top = Me.EMAIL1.Top - b
Me.EMAIL2.Top = Me.EMAIL1.Top - b

Me.ADDRESS1.Top = Me.ADDRESS1.Top - b
Me.ADDRESS2.Top = Me.ADDRESS1.Top - b
Me.CMD2.Top = Me.MOBILE1.Top
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD6.Top = Me.MOBILE1.Top
Me.CMD7.Top = Me.EMAIL1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.CMD2.Visible = True
Me.CMD6.Visible = False

End Sub

Private Sub CMD7_Click()
On Error Resume Next
a = 465.75
b = 30
Me.EMAIL2.Left = a
Me.EMAIL2.Top = Me.EMAIL1.Top - b
Me.ADDRESS1.Top = Me.ADDRESS1.Top - b
Me.ADDRESS2.Top = Me.ADDRESS2.Top - b
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.CMD3.Visible = True
Me.CMD7.Visible = False
End Sub

Private Sub CMD8_Click()
On Error Resume Next
a = 465.75
b = 30
Me.ADDRESS2.Left = a
Me.ADDRESS2.Top = Me.ADDRESS1.Top - b
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.CMD4.Visible = True
Me.CMD8.Visible = False
End Sub

Private Sub CommandButton2_Click()
Dim I As Long
I = Sheet2.Range("A1000000").End(xlUp).Row + 1
Sheet2.Range("A" & I) = Me.NMEBOX1
Sheet2.Range("B" & I) = Me.NMEBOX2
Sheet2.Range("C" & I) = Me.TELBOX1
Sheet2.Range("D" & I) = Me.TELBOX2
Sheet2.Range("E" & I) = Me.mobbox
Sheet2.Range("F" & I) = Me.mobbox2
Sheet2.Range("G" & I) = Me.EMBOX1
Sheet2.Range("H" & I) = Me.EMBOX2
Sheet2.Range("I" & I) = Me.ADDRESSBOX1
Sheet2.Range("j" & I) = Me.ADDRESSBOX2

'Fore Clear YTextbox
Dim ctr As Control
For Each ctr In UserForm1.Controls
On Error Resume Next
ctr.Value = ""
Next ctr
Me.NMEBOX1.SetFocus
End Sub



Friday, February 5, 2021

Create Cashbook Report Userform Excel VBA

Create Cashbook Report Userform Excel VBA

Create Cashbook Report Userform Excel VBA



VBA CODE
Private Sub ComboBox1_Change()
Me.ListBox1.Clear
Me.ListBox1.AddItem
For r = 1 To 9
Me.ListBox1.List(0, r - 1) = Sheet1.Cells(1, r)
Next r
Me.ListBox1.Selected(0) = True

Dim I As Long
For I = 2 To Sheet1.Range("A1000000").End(xlUp).Row
If Sheet1.Cells(I, "G") = CStr(Me.ComboBox1) And Sheet1.Cells(I, "A") = CDate(Me.TextBox1) Then
Me.ListBox1.AddItem
For c = 0 To 9
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(I, c + 1)
Next c
End If
Next I

Dim M As Integer
Dim SUM, SUM1 As Double
For M = 1 To Me.ListBox1.ListCount - 1
If Me.ListBox1.List(M, 6) = Me.ComboBox1 Then
SUM = SUM + Val(Me.ListBox1.List(M, 4))
SUM1 = SUM1 + Val(Me.ListBox1.List(M, 5))
End If
Next M
Me.ListBox1.AddItem Me.ComboBox1
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Total"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM, "#####.00")
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = Format(SUM1, "#####.00")

Me.ListBox1.AddItem "____________"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Balance"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM - SUM1, "#####.00")
SUM = 0
SUM1 = 0
End Sub

Private Sub ComboBox2_Change()
Me.ListBox1.Clear
Me.ListBox1.AddItem
For r = 1 To 9
Me.ListBox1.List(0, r - 1) = Sheet1.Cells(1, r)
Next r
Me.ListBox1.Selected(0) = True

Dim I As Long
For I = 2 To Sheet1.Range("A1000000").End(xlUp).Row
If Sheet1.Cells(I, "i") = CStr(Me.ComboBox2) And Sheet1.Cells(I, "A") = CDate(Me.TextBox1) Then
Me.ListBox1.AddItem
For c = 0 To 9
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(I, c + 1)
Next c
End If
Next I

Dim M As Integer
Dim SUM, SUM1 As Double
For M = 1 To Me.ListBox1.ListCount - 1
If Me.ListBox1.List(M, 8) = Me.ComboBox2 Then
SUM = SUM + Val(Me.ListBox1.List(M, 4))
SUM1 = SUM1 + Val(Me.ListBox1.List(M, 5))
End If
Next M
Me.ListBox1.AddItem Me.ComboBox2
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Total"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM, "#####.00")
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = Format(SUM1, "#####.00")

Me.ListBox1.AddItem "____________"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Balance"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM - SUM1, "#####.00")
SUM = 0
SUM1 = 0
End Sub

Private Sub CommandButton1_Click()
Me.ListBox1.Clear
Me.ListBox1.AddItem
For r = 1 To 9
Me.ListBox1.List(0, r - 1) = Sheet1.Cells(1, r)
Next r
Me.ListBox1.Selected(0) = True

Dim I As Long
Dim X As Integer
For X = 0 To Me.ComboBox1.ListCount - 1
For I = 2 To Sheet1.Range("A1000000").End(xlUp).Row
If Sheet1.Cells(I, "G") = CStr(Me.ComboBox1.List(X, 0)) And Sheet1.Cells(I, "A") = CDate(Me.TextBox1) Then
Me.ListBox1.AddItem
For c = 0 To 9
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(I, c + 1)
Next c
End If
Next I

Dim M As Integer
Dim SUM, SUM1 As Double
For M = 1 To Me.ListBox1.ListCount - 1
If Me.ListBox1.List(M, 6) = Me.ComboBox1.List(X, 0) Then
SUM = SUM + Val(Me.ListBox1.List(M, 4))
SUM1 = SUM1 + Val(Me.ListBox1.List(M, 5))
End If
Next M
Me.ListBox1.AddItem Me.ComboBox1.List(X, 0)
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Total"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM, "#####.00")
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = Format(SUM1, "#####.00")

Me.ListBox1.AddItem "____________"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Balance"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM - SUM1, "#####.00")
SUM = 0
SUM1 = 0
Next X
End Sub


Private Sub SpinButton1_SpinDown()
On Error Resume Next
Me.TextBox1 = CDate(Me.TextBox1) - 1
Call CommandButton1_Click
End Sub

Private Sub SpinButton1_SpinUp()
On Error Resume Next
Me.TextBox1 = CDate(Me.TextBox1) + 1
Call CommandButton1_Click
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1 = Date
Dim UID As New Collection
Dim I As Long
For I = 2 To Sheet1.Range("A1000000").End(xlUp).Row
On Error Resume Next
UID.Add CStr(Sheet1.Cells(I, "G")), CStr(Sheet1.Cells(I, "G"))
Next I
For Each itm In UID
Me.ComboBox1.AddItem itm
Next itm
Me.ComboBox2.List = Array("Cash", "Bank")
End Sub


Monday, January 25, 2021

Sales Invoice Video#5 Save To Database And Invoice Project Excel VBA

 Sales Invoice Video#5 Save To Database And Invoice Project Excel VBA

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