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