Tuesday, December 21, 2021
Sunday, December 12, 2021
Thursday, December 9, 2021
Monday, December 6, 2021
Thursday, December 2, 2021
Sunday, November 28, 2021
Thursday, November 25, 2021
Excel Payroll Entry Avoid Duplicate Month Entry Excel VBA
Monday, November 22, 2021
Monday, September 13, 2021
Thursday, June 3, 2021
ListView Checkboxes And Checked Item Userform Excel VBA
ListView Checkboxes And Checked Item Userform Excel VBA
Watch Video
Thursday, May 27, 2021
ListView tranfer data to Worksheet Excel VBA
ListView tranfer data to Worksheet Excel VBA
Watch Video
Monday, May 24, 2021
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 20, 2021
Listview Sum And Sum Running Total Excel VBA
Listview Sum And Sum Running Total Excel VBA
Watch Video
Monday, May 17, 2021
Thursday, May 13, 2021
Thursday, May 6, 2021
Listview Icons And Small icons Userform Excel VBA
Listview Icons And Small icons Userform Excel VBA
Watch Video
Monday, April 26, 2021
Listview Column Header And Data fill Excel VBA
Listview Column Header And Data fill Excel VBA
Watch Video
Monday, February 22, 2021
Create Hide And Seek Contact form Excel VBA
Create Hide And Seek Contact form Excel VBA
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
Thursday, February 18, 2021
Friday, February 5, 2021
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
Thursday, January 28, 2021
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
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
Saturday, January 23, 2021
Thursday, January 21, 2021
Subscribe to:
Posts (Atom)