How to create Cheque Drop Box in Excel through VBA

 CHEQUE DROP BOX IN EXCEL

Today we will learn How to create an Cheque Drop Box in Excel. In this tutorial I will discuss step by step.

First of all You need to design a clear plan in your mind when you want to build such application.

1. Create or Design a Userform by using toolbox in excel visual basic.

2. To create this I used three listbox and comboboxes and TextBoxes.

3. After design this You need to create a data entry form by which you can input your cheques which you received from your customer or clients. You can input multiple cheque through this data entry form. When you click on add button, the data will add on ListBox1. After completed your all cheque, when you click on Save button It will transfer your all data from ListBox1 to worksheet ( Here Worksheet Name is "ChequeDropBox").

4. create vba codes for ListBox2 And ListBox3 where you can see both cheque which are in your hand ( those cheque which are not deposited in Bank ) and which are send to Bank for clearing.

5. After receiving Bank response you need to change the status of that cheque clear or return.

6.If you need to to view all clear cheque or all return cheque, so there are two extra commandbutton.

Now See the vba codes which I used in this tutorials..

To download this file, please goto at end of this page. You will see download link

Userform initialization || Add Items on ComBoxes while run userforms

Private Sub UserForm_Initialize()

Dim ws As Worksheet

Set ws = Sheets("AddressBook")

Dim lastRow As Long

lastRow = ws.Range("A100000").End(xlUp).Row

Dim i As Long

For i = 2 To lastRow

Me.ComboBox1.AddItem ws.Cells(i, 2) & "-" & ws.Cells(i, 1)

Next

Me.TextBox5.Value = Format(Date, "dd-mmm-yy")

' to show Bank Name where you submit your cheque

With ComboBox2

.AddItem "SBI"

.AddItem "Bank of Baroda"

.AddItem " Axis Bank"

End With

End Sub

Add Data in ListBox from TextBoxes and ComboBoxes || Cheque Entry Form

Private Sub CommandButton1_Click()

' data add to listbox

With Me.ListBox1

 .AddItem Me.ComboBox1

 .List(.ListCount - 1, 1) = Me.TextBox1

 .List(.ListCount - 1, 2) = Me.TextBox2

 .List(.ListCount - 1, 3) = Me.TextBox3

 .List(.ListCount - 1, 4) = Me.TextBox4

End With

'Me.TextBox1 = ""

Me.TextBox2 = ""

Me.TextBox3 = ""

Me.TextBox4 = ""

End Sub

Data Transfer from ListBox to Worksheet || Keep Records in your Worksheet for Each Cheques

Private Sub CommandButton7_Click()

'' data transfer from

Dim sh As Worksheet

Set sh = Sheets("ChequeDropBox")

Dim i As Long

For i = 0 To ListBox1.ListCount - 1

With sh.Range("B1000000").End(xlUp)

.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)

.Offset(1, 4) = Me.ListBox1.List(i, 4)

End With

Next i

Me.ListBox1.Clear

'Unload Me

End Sub 

 Display Cheques details date wise by changing dates of TextBox

Private Sub TextBox5_Change()

Me.ListBox2.Clear

Dim ws As Worksheet

Set ws = Sheets("ChequeDropBox")

Dim lastRow As Long

lastRow = ws.Range("B100000").End(xlUp).Row

Dim i As Long

For i = 3 To lastRow

If Me.TextBox5.Value = CDate(ws.Cells(i, 5)) And ws.Cells(i, 7) = "" Then

With Me.ListBox2

.AddItem ws.Cells(i, 2)

.List(.ListCount - 1, 1) = ws.Cells(i, 3)

.List(.ListCount - 1, 2) = ws.Cells(i, 4)

.List(.ListCount - 1, 3) = Format(ws.Cells(i, 5), "dd-mmm-yy")

.List(.ListCount - 1, 4) = ws.Cells(i, 6)

End With

End If

Next

End Sub

Controll date for TextBox by Spinbutton

Private Sub SpinButton1_SpinDown()

On Error Resume Next

Me.TextBox5.Value = Format(CDate(Me.TextBox5.Value) - 1, "dd-mmm-yy")

End Sub

Private Sub SpinButton1_SpinUp()

On Error Resume Next

Me.TextBox5.Value = Format(CDate(Me.TextBox5.Value) + 1, "dd-mmm-yy")

End Sub

Private Sub TextBox3_AfterUpdate()

Me.TextBox3.Value = Format(Me.TextBox3, "DD-MMM-YY")

End Sub 

View all Cheques which are in your hand and need to transfer to Bank for Clearing || Here cheques will shows in ListBox2 which are to be send to bank and in LIstBox3 will show those cheques which are already sent to bank but yet no response from bank.

Private Sub CommandButton11_Click()

' view all cheque, not clear

Me.Label9.Caption = "Pending From Bank's End"

Me.ListBox2.Clear

Me.ListBox3.Clear

Dim ws As Worksheet

Set ws = Sheets("ChequeDropBox")

Dim r As Long

For r = 3 To ws.Range("B100000").End(xlUp).Row

If ws.Cells(r, 7) <> "Send to Bank" Then

With Me.ListBox2

.AddItem ws.Cells(r, 2) ' Name

.List(.ListCount - 1, 1) = ws.Cells(r, 3) ' Contact No

.List(.ListCount - 1, 2) = ws.Cells(r, 4) ' Cheque No

.List(.ListCount - 1, 3) = Format(ws.Cells(r, 5), "dd-mmm-yy") ' Chq date

.List(.ListCount - 1, 4) = ws.Cells(r, 6)

.List(.ListCount - 1, 5) = ws.Cells(r, 9)

End With

ElseIf ws.Cells(r, 7) = "Send to Bank" And ws.Cells(r, 10) = "" Then

With Me.ListBox3

.AddItem ws.Cells(r, 2) ' Name

.List(.ListCount - 1, 1) = ws.Cells(r, 3) ' Contact No

.List(.ListCount - 1, 2) = ws.Cells(r, 4) ' Cheque No

.List(.ListCount - 1, 3) = ws.Cells(r, 6) 'chq amount

.List(.ListCount - 1, 4) = Format(ws.Cells(r, 8), "dd-mmm-yy") 'Banking date

.List(.ListCount - 1, 5) = ws.Cells(r, 9)

End With

End If

Next r

End Sub 

VBA codes for Send to bank Commandbutton || Transfer data from one listbox to another

Private Sub CommandButton8_Click()

'cheque send to bank button

If Me.ListBox2.ListCount = 0 Then

MsgBox "No item displayed on listbox"

Exit Sub

End If

If Me.ComboBox2.Value = "" Then

MsgBox "Please Select Bank Name"

Exit Sub

End If

Dim i As Long

Dim ws As Worksheet

Set ws = Sheets("ChequeDropBox")

Dim x As Long, s As String

For i = 0 To Me.ListBox2.ListCount - 1

For x = 3 To ws.Range("B100000").End(xlUp).Row

If Me.ListBox2.Selected(i) = True Then

'MsgBox "Please Select listbox item"

'Exit Sub

'Else

If ws.Cells(x, 4) = Val(Me.ListBox2.Column(2)) Then

ws.Cells(x, 7) = "Send to Bank"

ws.Cells(x, 8) = Date ' banking date

ws.Cells(x, 9) = Me.ComboBox2.Value ' bank name

End If

End If

Next

Next

' to clear listbox data after send to Bank

Me.ComboBox2 = ""

'On Error Resume Next

Dim p As Integer

For p = 0 To 4

Me.ListBox2.Column(p) = ""

Next p

'display all cheque which is sent to bank but not clear

Me.ListBox3.Clear

Dim r As Long

For r = 3 To ws.Range("B100000").End(xlUp).Row

If ws.Cells(r, 7) = "Send to Bank" And ws.Cells(r, 10) = "" Then

With Me.ListBox3

.AddItem ws.Cells(r, 2) ' Name

.List(.ListCount - 1, 1) = ws.Cells(r, 3) ' Contact No

.List(.ListCount - 1, 2) = ws.Cells(r, 4) ' Cheque No

.List(.ListCount - 1, 3) = ws.Cells(r, 6) 'chq amount

.List(.ListCount - 1, 4) = Format(ws.Cells(r, 8), "dd-mmm-yy") 'Banking date

.List(.ListCount - 1, 5) = ws.Cells(r, 9)

End With

End If

Next r

End Sub

Edit Worksheet data from ListBox through Cheque number || for Clear commandbutton

Private Sub CommandButton9_Click()

Application.ScreenUpdating = False

'chq clear button

Dim i As Long

Dim x As Long

Dim ws As Worksheet

Set ws = Sheets("ChequeDropBox")

For i = 0 To Me.ListBox3.ListCount - 1

For x = 3 To ws.Range("B100000").End(xlUp).Row

If Me.ListBox3.Selected(i) = False Or Me.ListBox3.ListCount = False Then

Exit Sub

End If

If ws.Cells(x, 4) = Val(Me.ListBox3.Column(2)) Then

ws.Cells(x, 10) = "Clear"

End If

Next

Next

If Me.ListBox3.ListCount = False Then

Exit Sub

Else

End If

Dim r As Integer

For r = 0 To 5

Me.ListBox3.Column(r) = ""

Next r

End Sub

 VBA codes for return commandbutton

Private Sub CommandButton10_Click()

'chq return from bank

Dim i As Long

Dim x As Long

Dim ws As Worksheet

Set ws = Sheets("ChequeDropBox")

For i = 0 To Me.ListBox3.ListCount - 1

For x = 3 To ws.Range("B100000").End(xlUp).Row

If Me.ListBox3.Selected(i) = True Then

If ws.Cells(x, 4) = Val(Me.ListBox3.Column(2)) Then

ws.Cells(x, 10) = "Return"

End If

End If

Next

Next

If Me.ListBox3.ListCount = False Then

Exit Sub

Else

End If

On Error Resume Next

Dim p As Integer

For p = 0 To 5

Me.ListBox3.Column(p) = ""

Next p

End Sub

To view all cleared cheques || view data in listbox for certain criteria

Private Sub CommandButton13_Click()

' view all clear chq

Me.Label9.Caption = "List of All Clear Cheque"

Me.ListBox3.Clear

Dim ws As Worksheet

Set ws = Sheets("ChequeDropBox")

Dim r As Long

For r = 3 To ws.Range("B100000").End(xlUp).Row

If ws.Cells(r, 10) = "Clear" Then

With Me.ListBox3

.AddItem ws.Cells(r, 2) ' Name

.List(.ListCount - 1, 1) = ws.Cells(r, 3) ' Contact No

.List(.ListCount - 1, 2) = ws.Cells(r, 4) ' Cheque No

.List(.ListCount - 1, 3) = ws.Cells(r, 6) 'chq amount

.List(.ListCount - 1, 4) = Format(ws.Cells(r, 8), "dd-mmm-yy") 'Banking date

.List(.ListCount - 1, 5) = ws.Cells(r, 9)

End With

End If

Next r

End Sub

 View all return cheque details

Private Sub CommandButton12_Click()

' view all return cheque

Me.Label9.Caption = "List of All Return Cheque"

Me.ListBox3.Clear

Dim ws As Worksheet

Set ws = Sheets("ChequeDropBox")

Dim r As Long

For r = 3 To ws.Range("B100000").End(xlUp).Row

If ws.Cells(r, 10) = "Return" Then

With Me.ListBox3

.AddItem ws.Cells(r, 2) ' Name

.List(.ListCount - 1, 1) = ws.Cells(r, 3) ' Contact No

.List(.ListCount - 1, 2) = ws.Cells(r, 4) ' Cheque No

.List(.ListCount - 1, 3) = ws.Cells(r, 6) 'chq amount

.List(.ListCount - 1, 4) = Format(ws.Cells(r, 8), "dd-mmm-yy") 'Banking date

.List(.ListCount - 1, 5) = ws.Cells(r, 9)

End With

End If

Next r

End Sub

Please write your feedback

To download this file click here

For Supports Please Subscribe my Channel.

If You need any assistance regarding this file You can send emails also.