create Address Book in Excel vba [GST Billing Software]

 How to create Address Book or Contact Management in Excel vba according to GST BILLING.

Complete Accounting & GST Billing Software in Excel VBA

Part 1: Cheque Management System in Excel [ VBA] ---- [Published]

Part2: Create Address Book or Contact Management in Excel according to Accounting & GST Billing Software ---- [Published]

Upcoming Videos

Part3: Create a GST Billing format. Save it as pdf & keep this record in your workbook with hyperlink.

Part3: How to create Receipt Voucher & Payment Voucher without userform in Excel VBA & save it as pdf on your system through vba code.

Part4: Data transfer to Transaction Sheet & Due register if any one has due amount.

Part5: Purchase Bill Entry & Cheque Issuing to Suppliers [ Credit Note Entry, Debit Note Entry]

Part6: Multiple Bank Account Maintaining

Part7: Supplier Ledger Maintaining

Part8: Export Ledger as Pdf or excel file

& etc.

If want to get this video series as soon as possible then please support me by subscribing my Channel. Because your one support inspairs me a lot. If you want to learn, so don't miss this video series. I will explain each an every point. So that It can easy to understand for everyone. I hope Your bless and support will be with me.

Lets Start It.

1. Open an blank excel workbook ----> save as type ---> excel macro-enabled workbook.

2. Insert two worksheet and remaned it as Invoice & AddressBook

3. Design AddressBook As follow in the Video.

4. Goto Developer Tab. If you have not then click here

5. Insert userform

& Design it from toolbox [if you have not showing toolbox, then

click here] as follow in the video or shown in the below pic.

First of All double click on userform & convert it from Private Sub UserForm_Click() to 
Private Sub UserForm_Initialize()

and then write the below code

• Private Sub UserForm_Initialize()
• Me.TextBox1.Value = "100" & Sheets("AddressBook").Range("A100000").End(xlUp).Row + 1
• Dim sh As Worksheet
• Set sh = Sheets("AddressBook")
• Dim p As Long
• For p = 2 To sh.Range("n100").End(xlUp).Row
• Me.ComboBox1.AddItem sh.Cells(p, 14)
• Next p
• End Sub

Details are shown in the pic

TRANSFER DATA FROM USERFORM TO WORKSHEET [ create a data entry userform ]

•  Declare a worksheet through a variable where you want to transfer data. Here I will transfer data from userform to a worksheet named as "AddressBook" as follow

Dim sh As Worksheet

Set sh = Sheets("AddressBook")

    2. Find the row number where you want to input data. So that You need to find last non-empty row for a particular column then add (+1).

Dim i As Long

i = sh.Range("A100000").End(xlUp).Row + 1

3. Now count TextBoxes whose value will be transfered. Here noted that TextBoxes must be in same serial number as per your column headers

Dim x As Integer
For x = 1 To 11 

If you wish to set a condition that there will be no blank textboxes. so that a notification will appear through a msgbox. then

If Me("TextBox" & x) = "" Then
MsgBox "Please fill all field"
Exit Sub
End If 

4. Now you can transfer data from userform. Here you supposed your row number 6 find by i, then cells will be (6,1), (6,2), (6,3), (6,4), (6,5) .... (6,11)  and similarly TextBox1, TextBox2, TextBox3 .... TextBox11

sh.Cells(i, x).Value = Me("TextBox" & x).Value 

[If You could not maintaned then TextBoes serial wise as per your worksheet Headers. Then you can write below codes. 

sh.Cells(i, 1).Value = Me.TextBox1.Value 
sh.Cells(i, 2).Value = Me.TextBox2.Value 
sh.Cells(i, 3).Value = Me.TextBox3.Value 
sh.Cells(i, 4).Value = Me.TextBox4.Value 
sh.Cells(i, 5).Value = Me.TextBox5.Value 
sh.Cells(i, 6).Value = Me.TextBox6.Value 
sh.Cells(i, 7).Value = Me.TextBox7.Value 
sh.Cells(i, 8).Value = Me.TextBox8.Value 
sh.Cells(i, 9).Value = Me.TextBox9.Value 
sh.Cells(i, 10).Value = Me.TextBox10.Value 

sh.Cells(i, 11).Value = Me.TextBox11.Value 

Now Transfer ComboBox's value in column number 12 by

        sh.Cells(i, 12).Value = Me.ComboBox1.Value

********** Hence your data entry part is completed **********

Search Contact List in ListBox through a TextBox. Randomly change data in listbox while typing on TextBox

You can input this vba code by double clicking on TextBox12 and change it into        Private Sub TextBox12_Change()

 or You can create a seperate moudle. Here I create a seperate module because I used it in multiple section

Sub show_data_inListBox()
Dim sh As Worksheet
Set sh = Sheets("AddressBook")
Dim i, p, x, a As Long
UserForm1.ListBox1.Clear

' ListBox Headers
With UserForm1.ListBox1
.AddItem "Cust-ID"
.List(0, 1) = "Customer Name"
.List(0, 2) = "Mobile No."
.List(0, 3) = "Address"
.List(0, 4) = "Land Mark"
.List(0, 5) = "City"
.List(0, 6) = "State Name"
.List(0, 7) = "Code"
.List(0, 8) = "PIN"
.List(0, 9) = "GST No."
.Selected(0) = True
End With

' Range identify through this variable
For i = 2 To sh.Range("A100000").End(xlUp).Row

'Charecter count in TextBox
For x = 1 To Len(UserForm1.TextBox12.Text)

'TextBox Length counting
a = UserForm1.TextBox12.textLength

'Condition apply
If UCase(Mid(sh.Cells(i, 2), x, a)) = UserForm1.TextBox12.Text And UserForm1.TextBox12.Value <> "" Then

'Search the values based on TextBox12
With UserForm1.ListBox1
.AddItem sh.Cells(i, 1)
.List(.ListCount - 1, 1) = sh.Cells(i, 2) 'Cust Name
.List(.ListCount - 1, 2) = sh.Cells(i, 3) ' Mobile No
.List(.ListCount - 1, 3) = sh.Cells(i, 4) ' Address
.List(.ListCount - 1, 4) = sh.Cells(i, 5) ' Land mark
.List(.ListCount - 1, 5) = sh.Cells(i, 6) ' City
.List(.ListCount - 1, 6) = sh.Cells(i, 7) ' State
.List(.ListCount - 1, 7) = sh.Cells(i, 8) ' Code
.List(.ListCount - 1, 8) = sh.Cells(i, 9) ' PIN
.List(.ListCount - 1, 9) = sh.Cells(i, 10) ' GSTIN

End With
End If
Next x
Next i
End Sub

Now Call this Moudle whose macro name is show_data_inListBox by double click on TextBox12 as follow

Private Sub TextBox12_Change()
Me.TextBox12 = UCase(Me.TextBox12) '  Convert letter into upper case
Me.TextBox2 = Me.TextBox12 ' while typing in TextBox2, it will reflect on TextBox12
Call show_data_inListBox ' now call this macro
End Sub

Data Transfer from ListBox to TextBoxes. If you need to update or modify your worksheet data  

In this case Double click on ListBox1 and type the below vba code

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim r As Long

For r = 1 To Me.ListBox1.ListCount - 1

If Me.ListBox1.Selected(r) = True Then

Me.TextBox1.Value = Me.ListBox1.Column(0)

End If

Next r

'--- Search Data from Worksheet based on TextBox's value--

    Dim sh As Worksheet

    Set sh = Sheets("AddressBook")

    Dim i As Long

    For i = 2 To sh.Range("A100000").End(xlUp).Row

    If sh.Cells(i, 1) = Me.TextBox1.Text Then

    Dim x As Integer

    For x = 2 To 11

    Me("TextBox" & x).Value = sh.Cells(i, x)

    Next x

    Me.ComboBox1.Value = sh.Cells(i, 12)

    End If

    Next i

Me.CommandButton3.Enabled = False

End Sub

Now Updating or Modifying data using this userform through Commandbutton4

Now double click on Commandbutton4 and write the below vba codes. In above case I used a method to show data in TextBox from worksheet based on Customer ID. Here sense will be opposite. So that vba code will remain almost same just change the highlighted line as follow

Dim sh As Worksheet

Set sh = Sheets("AddressBook")

Dim i As Long

For i = 2 To sh.Range("A100000").End(xlUp).Row

If sh.Cells(i, 1) = Me.TextBox1.Text Then

Dim x As Integer

For x = 2 To 11

 sh.Cells(i, x).Value = Me("TextBox" & x).Value

 Me("TextBox" & x) = ""

Next x

sh.Cells(i, 12).Value = Me.ComboBox1.Value

End If

Next i

Me.TextBox1 = ""

Unload Me 

End Sub

Now I will searh data through mobile number while typing on TextBox3 

Just copy the vba codes from moudle which I created to search by Customer Name and paste it by double clicking on TextBox3 and Convert it TextBox3_Change() and change the codes in few

• Private Sub TextBox3_Change()
• If Not IsNumeric(Me.TextBox3) Then
• MsgBox "Only Mumeric Number allowed"
• Me.TextBox3 = ""
• Exit Sub
• 
  
• End If
• 
  
• Dim sh As Worksheet
• Set sh = Sheets("AddressBook")
• Dim i, p As Long
• Me.ListBox1.Clear
• 
  
• With Me.ListBox1
• .AddItem "Cust-ID"
• .List(0, 1) = "Customer Name"
• .List(0, 2) = "Mobile No."
• .List(0, 3) = "Address"
• .List(0, 4) = "Land Mark"
• .List(0, 5) = "City"
• .List(0, 6) = "State Name"
• .List(0, 7) = "Code"
• .List(0, 8) = "PIN"
• .List(0, 9) = "GST No."
• .Selected(0) = True
• End With
• 
  
• For i = 2 To sh.Range("A100000").End(xlUp).Row
• For x = 1 To Len(Me.TextBox3.Text)
• a = Me.TextBox3.textLength
• If Mid(sh.Cells(i, 3), x, a) = Me.TextBox3.Text And Me.TextBox3.Value <> "" Then
• With Me.ListBox1
• .AddItem sh.Cells(i, 1)
• .List(.ListCount - 1, 1) = sh.Cells(i, 2) 'Cust Name
• .List(.ListCount - 1, 2) = sh.Cells(i, 3) ' Mobile No
• .List(.ListCount - 1, 3) = sh.Cells(i, 4) ' Address
• .List(.ListCount - 1, 4) = sh.Cells(i, 5) ' Land mark
• .List(.ListCount - 1, 5) = sh.Cells(i, 6) ' City
• .List(.ListCount - 1, 6) = sh.Cells(i, 7) ' State
• .List(.ListCount - 1, 7) = sh.Cells(i, 8) ' Code
• .List(.ListCount - 1, 8) = sh.Cells(i, 9) ' PIN
• .List(.ListCount - 1, 9) = sh.Cells(i, 10) ' GSTIN
• End With
• End If
• Next x
• Next i
• End Sub

Here [Mid(sh.Cells(i, 3), x, a)] 3 means column number i.e column C of addressBook where mobile number has been stored.

Now Transfer Contact Details To Invoice from ListBox

To insert contact details into invoice format, Select ListBox data and click Ok button. So now we will coding for commandbutton2 i.e Ok button. double click on it and follow the below vba codes

• Private Sub CommandButton2_Click()
• 
  
• Dim r As Long
• For r = 1 To Me.ListBox1.ListCount - 1
• 
  
• If Me.ListBox1.Selected(r) = True Then
• With Sheets("Invoice")
• .Range("C9").Value = Me.ListBox1.List(r, 1)
• .Range("C10").Value = Me.ListBox1.List(r, 2) _
•  & Chr(10) & Me.ListBox1.List(r, 3) & Chr(10) & Me.ListBox1.List(r, 4) _
•  & Chr(10) & Me.ListBox1.List(r, 5) & Chr(10) & Me.ListBox1.List(r, 8) _
•  & Chr(10) & "GSTIN :" & Me.ListBox1.List(r, 9)
• .Range("C16").Value = "State Name:" & Me.ListBox1.List(r, 6)
• .Range("E16").Value = Me.ListBox1.List(r, 7)
• .Range("H9").Value = Format(Date, "dd-mmm-yy")
• 
  
• End With
• End If
• 
  
• Next r
• Unload Me
• End Sub

If you want to search more videos or subscribe my channel then goto my YouTube Channel and Subscribe first. If you want to get my next Video on GST Billing, comment me. Thanks for watching this.