Thursday, April 16, 2020

Create Income Expense Form In Excel VBA

Down Load Android App  

Income Expense   







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

2 comments:

  1. Thanks Sir.
    Can you send me a copy.

    i am beginner. And i need to make practice.

    Thanks you again.

    ReplyDelete

Please do not enter any spam message in comment box