VBA code
| Ledger Form |
| Me.ComboBox1.DropDown |
| End Sub |
| Private Sub CommandButton1_Click() |
| Dim MROW As Long |
| If Me.TextBox1 <> "" And Me.TextBox1 <> "" Then |
| MROW = Sheet3.Range("A1000000").End(xlUp).Row + 1 |
| Sheet3.Range("A" & MROW) = Me.TextBox1 |
| Sheet3.Range("B" & MROW) = Me.ComboBox1 |
| Me.TextBox1 = "" |
| Me.ComboBox1 = "" |
| Me.TextBox1.SetFocus |
| Else |
| MsgBox "Please Fill name And Group" |
| End If |
| End Sub |
| Private Sub TextBox1_AfterUpdate() |
| On Error Resume Next |
| A = Application.WorksheetFunction.Match(Me.TextBox1, Sheet3.Range("A:A"), 0) |
| If A >= 1 Then |
| MsgBox "This Name already exist" |
| Me.TextBox1 = "" |
| End If |
| Me.TextBox1.SetFocus |
| End Sub |
| Private Sub TextBox1_Change() |
| On Error Resume Next |
| Me.TextBox1 = Format(StrConv(Me.TextBox1, vbProperCase)) |
| End Sub |
| Private Sub UserForm_Initialize() |
| Me.ComboBox1.List = Array("Income", "Expense") |
| End Sub |
| TransForm |
| Private Sub CashMB_Enter() |
| Me.CashMB.DropDown |
| End Sub |
| Private Sub CommandButton1_Click() |
| Dim MROW As Long |
| Dim CLN As String |
| 'Input Data |
| If Me.LedgerCMB = "" And Me.AmtBox = "" Then |
| MsgBox "Please input amount And name " |
| Me.GroupCMB.SetFocus |
| Exit Sub |
| Else |
| CLN = Me.CLNBOX 'Textbox for Column Address |
| MROW = Sheet2.Range("A1000000").End(xlUp).Row + 1 |
| Sheet2.Range("A" & MROW) = Format(CDate(Me.TextBox1), "DD/MMM/YYYY") |
| Sheet2.Range("B" & MROW) = Me.LedgerCMB |
| Sheet2.Range("C" & MROW) = Me.GroupCMB |
| Sheet2.Range(CLN & MROW) = Me.AmtBox.Value |
| Sheet2.Range("F" & MROW) = Me.CashMB |
| Sheet2.Range("G" & MROW) = MROW + 100 |
| 'Form Clear |
| Me.GroupCMB = "" |
| Me.LedgerCMB = "" |
| Me.AmtBox = "" |
| Me.CashMB = "" |
| Me.CLNBOX = "" |
| Me.TextBox1.SetFocus |
| End If |
| End Sub |
| Private Sub GroupCMB_Change() |
| Dim I As Long |
| Me.LedgerCMB.Clear |
| For I = 2 To Sheet3.Range("A1000000").End(xlUp).Row |
| If Sheet3.Cells(I, "B") = Me.GroupCMB Then |
| Me.LedgerCMB.AddItem Sheet3.Cells(I, "A") |
| End If |
| Next I |
| If Me.GroupCMB = "Income" Then |
| Me.CLNBOX = "D" |
| ElseIf Me.GroupCMB = "Expense" Then |
| Me.CLNBOX = "E" |
| End If |
| End Sub |
| Private Sub GroupCMB_Enter() |
| Me.GroupCMB.DropDown |
| End Sub |
| Private Sub LedgerCMB_Enter() |
| Me.LedgerCMB.DropDown |
| End Sub |
| Private Sub TextBox1_AfterUpdate() |
| On Error Resume Next |
| Me.TextBox1 = CDate(Me.TextBox1) |
| End Sub |
| Private Sub TextBox1_Enter() |
| Me.TextBox1.SelStart = (0) |
| Me.TextBox1.SelLength = Len(Me.TextBox1) |
| End Sub |
| Private Sub UserForm_Initialize() |
| Me.GroupCMB.List = Array("Income", "Expense") 'Combobox |
| Me.CashMB.List = Array("Cash", "Bank") 'Combobox |
| Me.TextBox1 = Date |
| End Sub |
HomeForm |
| Private Sub CommandButton1_Click() |
| TransForm.Show |
| End Sub |
| Private Sub CommandButton5_Click() |
| With Me.ListBox1 |
| .Clear |
| .AddItem "Income" |
| .List(.ListCount - 1, 1) = "Cash" |
| .List(.ListCount - 1, 2) = "Bank" |
| .List(.ListCount - 1, 3) = "Expense" |
| .List(.ListCount - 1, 4) = "Cash" |
| .List(.ListCount - 1, 5) = "Bank" |
| .Selected(0) = True |
| 'Income Fill |
| Dim I As Long |
| Dim RNG As Long |
| RNG = Sheet3.Range("A1000000").End(xlUp).Row + 1 |
| For I = 2 To RNG |
| If Sheet3.Cells(I, "B") = "Income" Then |
| .AddItem Sheet3.Cells(I, "A") |
| End If |
| Next I |
| 'Expense Fill |
| For X = 1 To RNG |
| If Sheet3.Cells(X, "B") = "Expense" Then |
| .AddItem |
| M = M + 1 |
| .List(M, 3) = Sheet3.Cells(X, "A") |
| End If |
| Next X |
| 'Title Down |
| .AddItem |
| .List(.ListCount - 2, 0) = "Income" |
| .List(.ListCount - 2, 1) = "Cash" |
| .List(.ListCount - 2, 2) = "Bank" |
| .List(.ListCount - 2, 3) = "Expense" |
| .List(.ListCount - 2, 4) = "Cash" |
| .List(.ListCount - 2, 5) = "Bank" |
| 'UnderLine |
| .AddItem |
| For b = 0 To 5 |
| .List(.ListCount - 2, b) = "------------------------" |
| Next b |
| 'Date |
| Dim EDate, ENDate As Date |
| 'Start Date |
| EDate = CDate(Application.WorksheetFunction.EoMonth(1 & "/" & Me.MonthCMB & "/" & Me.YearCMB, -1) + 1) |
| 'End Date |
| ENDate = CDate(Application.WorksheetFunction.EoMonth(1 & "/" & Me.MonthCMB & "/" & Me.YearCMB, 0)) |
| Dim RNG2 As Long |
| Dim sum, sum1, sum4, sum5 As Double |
| Dim C, C1, C2, C3 As Integer |
| Dim A, A1, A2, A3 As Long |
| RNG2 = Sheet2.Range("A1000000").End(xlUp).Row + 1 |
| For C = 1 To .ListCount - 1 |
| For A = 1 To RNG2 |
| 'Sum from Sheet2 |
| 'Cash Income Total |
| If .List(C, 0) = Sheet2.Cells(A, "B") And Sheet2.Cells(A, "A") >= _ |
| EDate And Sheet2.Cells(A, "A") <= ENDate And Sheet2.Cells(A, "F") = "Cash" Then |
| sum = sum + Val(Sheet2.Cells(A, "D")) |
| .List(C, 1) = Format(sum, "#####.00") |
| End If |
| Next A |
| sum = 0 |
| Next C |
| 'Sum from Sheet2 |
| 'Bank Bank Income Total |
| For C1 = 1 To .ListCount - 1 |
| For A1 = 1 To RNG2 |
| If .List(C1, 0) = Sheet2.Cells(A1, "B") And Sheet2.Cells(A1, "A") >= _ |
| EDate And Sheet2.Cells(A1, "A") <= ENDate And Sheet2.Cells(A1, "F") = "Bank" Then |
| sum4 = sum4 + Val(Sheet2.Cells(A1, "D")) |
| .List(C1, 2) = Format(sum4, "#####.00") |
| End If |
| Next A1 |
| sum4 = 0 |
| Next C1 |
| 'Sum from Sheet2 |
| 'Expens Cash Total |
| For C2 = 1 To .ListCount - 1 |
| For A2 = 1 To RNG2 |
| If .List(C2, 3) = Sheet2.Cells(A2, "B") And Sheet2.Cells(A2, "A") >= _ |
| EDate And Sheet2.Cells(A2, "A") <= ENDate And Sheet2.Cells(A2, "F") = "Cash" Then |
| sum1 = sum1 + Val(Sheet2.Cells(A2, "E")) |
| .List(C2, 4) = Format(sum1, "#####.00") |
| End If |
| Next A2 |
| sum1 = 0 |
| Next C2 |
| 'Sum from Sheet2 |
| 'Expense Bank Total |
| For C3 = 1 To .ListCount - 1 |
| For A3 = 1 To RNG2 |
| If .List(C3, 3) = Sheet2.Cells(A3, "B") And Sheet2.Cells(A3, "A") >= _ |
| EDate And Sheet2.Cells(A3, "A") <= ENDate And Sheet2.Cells(A3, "F") = "Bank" Then |
| sum5 = sum5 + Val(Sheet2.Cells(A3, "E")) |
| .List(C3, 5) = Format(sum5, "#####.00") |
| End If |
| Next A3 |
| sum5 = 0 |
| Next C3 |
| 'Sum from Listbox |
| Dim N As Integer |
| Dim Sum2, sum3, sum6, sum7 As Double |
| For N = 1 To .ListCount - 1 |
| On Error Resume Next |
| Sum2 = Sum2 + Val(.List(N, 1)) 'Income Cash |
| sum3 = sum3 + Val(.List(N, 4)) 'Expense Cash |
| sum6 = sum6 + Val(.List(N, 2)) 'Income Bank |
| sum7 = sum7 + Val(.List(N, 5)) 'Expense Bank |
| Next N |
| 'Sum Total year |
| Dim YrIncSum, YrExpSum As Double |
| Dim STR, EDT As Date |
| STR = 1 & "/" & "January" & "/" & Me.YearCMB |
| EDT = 31 & "/" & "December" & "/" & Me.YearCMB |
| 'sum Income Year |
| YrIncSum = Application.WorksheetFunction.SumIfs(Sheet2.Range("D:D"), Sheet2.Range("A:A"), ">=" & _ |
| STR, Sheet2.Range("A:A"), "<=" & EDT) |
| 'Sum Espense Year |
| YrExpSum = Application.WorksheetFunction.SumIfs(Sheet2.Range("E:E"), Sheet2.Range("A:A"), ">=" & _ |
| STR, Sheet2.Range("A:A"), "<=" & EDT) |
| 'Income |
| .AddItem |
| .List(.ListCount - 2, 0) = "Total Income" 'Text column 0 |
| .List(.ListCount - 2, 1) = Format(Sum2, "####.00") 'Cash Income column1 |
| .List(.ListCount - 2, 2) = Format(sum6, "####.00") 'Bank Income column2 |
| 'Expense |
| .List(.ListCount - 2, 3) = "Total Expense" 'Text column3 |
| .List(.ListCount - 2, 4) = Format(sum3, "#####.00") ' Cash Expense column4 |
| .List(.ListCount - 2, 5) = Format(sum7, "####.00") ' Bank Expense column5 |
| 'Grand Total Selected month |
| .AddItem |
| .List(.ListCount - 2, 0) = Me.MonthCMB 'combobox(month) |
| .List(.ListCount - 2, 1) = "Grand Total " |
| .List(.ListCount - 2, 2) = Format(Sum2 + sum6, "####.00") 'Income Cash+Income Bank |
| .List(.ListCount - 2, 3) = Me.MonthCMB 'Combobox(Year) |
| .List(.ListCount - 2, 4) = "Grand Total " |
| .List(.ListCount - 2, 5) = Format(sum3 + sum7, "####.00") 'Expense Cash + Expense Bank |
| 'Undedrline |
| .AddItem "_________________" |
| .List(.ListCount - 2, 0) = "_________________" |
| .List(.ListCount - 2, 1) = "_________________" |
| .List(.ListCount - 2, 2) = "_________________" |
| .List(.ListCount - 2, 3) = "_________________" |
| .List(.ListCount - 2, 4) = "_________________" |
| .List(.ListCount - 2, 5) = "_________________" |
| 'Month Balance |
| .AddItem |
| .List(.ListCount - 2, 0) = Me.MonthCMB & " Cash In Hand" |
| .List(.ListCount - 2, 1) = Format(Val(Sum2 - sum3), "#####.00") 'Income Cash - Expense Cash |
| .List(.ListCount - 2, 2) = "Year" |
| .List(.ListCount - 2, 3) = "Income" |
| .List(.ListCount - 2, 4) = Format(YrIncSum, "#####.00") 'Year Income Total |
| 'Year Balance Income-Expense |
| .AddItem |
| .List(.ListCount - 2, 0) = Me.MonthCMB & " In Bank" |
| .List(.ListCount - 2, 1) = Format(Val(sum6 - sum7), "#####.00") 'Income Bank - Expense bank |
| .List(.ListCount - 2, 2) = Me.YearCMB 'Combobx Year |
| .List(.ListCount - 2, 3) = "Expense" |
| .List(.ListCount - 2, 4) = Format(YrExpSum, "#####.00") 'Year Total Expense |
| 'Total Cash + Bank Month |
| BLN = Val(Sum2 - sum3) + Val(sum6 - sum7) |
| .AddItem |
| .List(.ListCount - 2, 0) = "Total" |
| .List(.ListCount - 2, 1) = Format(BLN, "#####.00") |
| .List(.ListCount - 2, 3) = "Balance" |
| .List(.ListCount - 2, 4) = Format(Val(YrIncSum - YrExpSum), "#####.00") |
| End With |
| End Sub |
| Private Sub CommandButton3_Click() |
| LedgersForm.Show |
| End Sub |
| Private Sub CommandButton4_Click() |
| Dim EDate, ENDate As Date |
| 'Start Date |
| EDate = CDate(Application.WorksheetFunction.EoMonth(1 & "/" & _ |
| Me.MonthCMB & "/" & Me.YearCMB, -1) + 1) |
| 'End Date |
| ENDate = CDate(Application.WorksheetFunction.EoMonth(1 & "/" & _ |
| Me.MonthCMB & "/" & Me.YearCMB, 0)) |
| 'Listbox Header |
| With Me.ListBox1 |
| .Clear |
| .AddItem |
| For X = 0 To 6 'Loop Column |
| .List(0, X) = Sheet2.Cells(1, X + 1) |
| Next X |
| .Selected(0) = True |
| 'Listbox Fill |
| Dim I As Long |
| For I = 2 To Sheet2.Range("A1000000").End(xlUp).Row + 1 |
| If Sheet2.Cells(I, "A") >= EDate And Sheet2.Cells(I, "A") <= ENDate Then |
| .AddItem |
| For C = 0 To 6 'Loop Column |
| .List(.ListCount - 1, C) = Sheet2.Cells(I, C + 1) |
| Next C |
| End If |
| Next I |
| 'Sum Total |
| Dim R As Integer |
| Dim sum, sum1 As Double |
| For R = 1 To .ListCount - 1 |
| sum = sum + Val(.List(R, 3)) 'Amount In |
| sum1 = sum1 + Val(.List(R, 4)) 'Amount out |
| Next R |
| 'UnderLine |
| .AddItem |
| .List(.ListCount - 1, 3) = "----------------------" |
| .List(.ListCount - 1, 4) = "----------------------" |
| .List(.ListCount - 1, 5) = "----------------------" |
| On Error Resume Next |
| .AddItem |
| .List(.ListCount - 1, 3) = Format(sum, "#####.00") 'Total Amount in |
| .List(.ListCount - 1, 4) = Format(sum1, "#####.00") 'Total Amount Out |
| .List(.ListCount - 1, 5) = "Balance: " & Format(Val(sum - sum1), "#####.00") 'Balance |
| End With |
| End Sub |
| Private Sub UserForm_Initialize() |
| For I = 0 To 11 |
| Me.YearCMB.AddItem Format(Date, "YYYY") - I |
| Me.MonthCMB.AddItem MonthName(I + 1) |
| Next I |
| Me.YearCMB = Format(Date, "YYYY") 'Combobox |
| Me.MonthCMB = Format(Date, "MMMM") 'Combobox |
| End Sub |
Thanks Sir.
ReplyDeleteCan you send me a copy.
i am beginner. And i need to make practice.
Thanks you again.
Excellent
ReplyDelete