How to automate the creation of any invoice with a user-form and VBA. Today we learn how to work with user-forms with appropriate coding of the command buttons to transfer data to the worksheet, perform formatting and calculations and create any invoice automatically. We use two user-forms to make the understanding of the process easier. The first user-form with the relevant VBA code helps to create the basic invoice. The second user-form helps us understand how to perform calculations and use a function like the SpellNumber to complete the invoice. Although the concept helps to create an invoice according to the new Goods and Services Tax introduced in India, it can be used to create any invoice automatically and easily. Watch the video before studying the code or downloading the attached file:

Watch this video on YouTube.

Here’s the complete VBA code:

Private Sub cmdCreateInvoice_Click()

Cells.Clear

Range(“A1”) = lblTaxInvoice

Range(“A1:I2”).Select

With Selection

.Font.Name = “Calibri”

.Font.Size = 14

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.MergeCells = True

End With

Range(“A3”) = lblProvider

Range(“A3:I7”).Select

With Selection

.MergeCells = True

.Font.Name = “Calibri”

.Font.Size = 12

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = True

End With

Range(“A8”) = lblNameAddress

‘Range(“A8”).Font.Bold = True

Range(“A9″) = txtNameAddress & ” ” & “GSTIN” & “-” & txtGSTIN

Range(“A9:E15”).Select

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlTop

.WrapText = True

.MergeCells = True

End With

Range(“F8”) = lblDeliveryPlace

‘Range(“F8”).Font.Bold = True

Range(“F9”) = txtDeliveryPlace

Range(“F9:J15”).Select

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlTop

.WrapText = True

.MergeCells = True

End With

Range(“A16”) = lblInvoiceNum

Range(“B16”) = txtInvoiceNum

Range(“C16”) = lblInvoiceDate

Range(“D16”) = txtInvoiceDate

Range(“A18”) = UserForm2.lblSerialNum

‘Range(“A18”).Font.Bold = True

Range(“B18”) = UserForm2.lblDescription

Range(“B18”).Select

Range(“C18”) = UserForm2.lblHsnSac

Range(“D18”) = UserForm2.lblQty

Range(“E18”) = UserForm2.lblUnitPrice

Range(“F18”) = UserForm2.lblAmount

Range(“G18”) = UserForm2.lblCGST

Range(“H18”) = UserForm2.lblSGST

Range(“I18”) = UserForm2.lblIGST

Cells(19, 1).Select

Me.Hide

UserForm2.Show

End Sub

Private Sub cmdAddData_Click()

Dim lastrow As Long

lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row

‘lastRow = Cells.Find(What:=” * “, After:=Range(“A1”), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

‘MsgBox lastRow

Cells(lastrow + 1, 1) = txtSerialNum

Cells(lastrow + 1, 2) = txtDescription

Cells(lastrow + 1, 3) = txtHsnSac

Cells(lastrow + 1, 4) = Val(txtQty)

Cells(lastrow + 1, 5) = Val(txtUnitPrice)

txtAmount = Val(txtQty) * Val(txtUnitPrice)

Cells(lastrow + 1, 6) = Val(txtAmount)

Dim IGST As Double, CGST As Double, SGST As Double, GST As Double

IGST = 0

GST = 0

CGST = 0

SGST = 0

Dim intOption As Integer, optCaption As String

For intOption = 1 To 5

If Controls(“OptionButton” & intOption) = True And CheckBox1 = True Then

IGST = Val(txtAmount) * (Controls(“OptionButton” & intOption).Caption) / 100

txtIGST = IGST

ElseIf Controls(“OptionButton” & intOption) = True Then

GST = Val(txtAmount) * (Controls(“OptionButton” & intOption).Caption) / 100

CGST = GST / 2

txtCGST = CGST

SGST = GST / 2

txtSGST = GST / 2

End If

Next intOption

Cells(lastrow + 1, 7) = CGST

Cells(lastrow + 1, 8) = SGST

Cells(lastrow + 1, 9) = IGST

txtSerialNum = “”

txtDescription = “”

txtHsnSac = “”

txtQty = “”

txtUnitPrice = “”

txtAmount = “”

txtIGST = “”

txtCGST = “”

txtSGST = “”

End Sub

Private Sub cmdComplete_Click()

Dim i As Long, lastrow As Long, totalAmount As Double, totalIGST As Double, totalCGST As Double, totalSGST As Double

lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row

totalAmount = 0

totalCGST = 0

totalSGST = 0

totalIGST = 0

For i = 19 To lastrow

totalAmount = totalAmount + Cells(i, 6)

Cells(lastrow + 1, 6) = totalAmount

totalCGST = totalCGST + Cells(i, 7)

Cells(lastrow + 1, 7) = totalCGST

totalSGST = totalSGST + Cells(i, 8)

Cells(lastrow + 1, 8) = totalCGST

totalIGST = totalIGST + Cells(i, 9)

Cells(lastrow + 1, 9) = totalIGST

Next i

Cells(lastrow + 1, 1) = “Total”

lastrow = lastrow + 1

Cells(lastrow + 1, 1) = “Total Invoice Value with GST”

Cells(lastrow + 1, 2) = Cells(lastrow, 6) + Cells(lastrow, 7) + Cells(lastrow, 8) + Cells(lastrow, 9)

lastrow = lastrow + 1

Dim MyNumber As Double

MyNumber = Cells(lastrow, 2)

‘MsgBox MyNumber

Cells(lastrow + 1, 1) = “Amount in Words: ” & SpellNumber(MyNumber)

Range(Cells(lastrow + 1, 1), Cells(lastrow + 5, 5)).Select

With Selection

.MergeCells = True

.WrapText = True

End With

Cells(lastrow + 1, 6) = “For ABC Ltd.” & Chr(10) & Chr(10) & Chr(10) & “Authorised Signatory”

Range(Cells(lastrow + 1, 6), Cells(lastrow + 6, 9)).Select

With Selection

.MergeCells = True

.WrapText = True

End With

End Sub

Code for SpellNumber:

Option Explicit

‘Main Function

Function SpellNumber(ByVal MyNumber)

Dim Rupees, Paise, Temp

Dim DecimalPlace, Count

ReDim Place(9) As String

Place(2) = ” Thousand ”

Place(3) = ” Million ”

Place(4) = ” Billion ”

Place(5) = ” Trillion ”

‘ String representation of amount.

MyNumber = Trim(Str(MyNumber))

‘ Position of decimal place 0 if none.

DecimalPlace = InStr(MyNumber, “.”)

‘ Convert Paise and set MyNumber to dollar amount.

If DecimalPlace > 0 Then

Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _

“00”, 2))

MyNumber = Trim(Left(MyNumber, DecimalPlace – 1))

End If

Count = 1

Do While MyNumber <> “”

Temp = GetHundreds(Right(MyNumber, 3))

If Temp <> “” Then Rupees = Temp & Place(Count) & Rupees

If Len(MyNumber) > 3 Then

MyNumber = Left(MyNumber, Len(MyNumber) – 3)

Else

MyNumber = “”

End If

Count = Count + 1

Loop

Select Case Rupees

Case “”

Rupees = “No Rupees”

Case “One”

Rupees = “One Rupee”

Case Else

Rupees = Rupees & ” Rupees”

End Select

Select Case Paise

Case “”

Paise = ” and No Paise”

Case “One”

Paise = ” and One Paisa”

Case Else

Paise = ” and ” & Paise & ” Paise”

End Select

SpellNumber = Rupees & Paise

End Function

‘ Converts a number from 100-999 into text

Function GetHundreds(ByVal MyNumber)

Dim Result As String

If Val(MyNumber) = 0 Then Exit Function

MyNumber = Right(“000” & MyNumber, 3)

‘ Convert the hundreds place.

If Mid(MyNumber, 1, 1) <> “0” Then

Result = GetDigit(Mid(MyNumber, 1, 1)) & ” Hundred ”

End If

‘ Convert the tens and ones place.

If Mid(MyNumber, 2, 1) <> “0” Then

Result = Result & GetTens(Mid(MyNumber, 2))

Else

Result = Result & GetDigit(Mid(MyNumber, 3))

End If

GetHundreds = Result

End Function

‘ Converts a number from 10 to 99 into text.

Function GetTens(TensText)

Dim Result As String

Result = “” ‘ Null out the temporary function value.

If Val(Left(TensText, 1)) = 1 Then ‘ If value between 10-19…

Select Case Val(TensText)

Case 10: Result = “Ten”

Case 11: Result = “Eleven”

Case 12: Result = “Twelve”

Case 13: Result = “Thirteen”

Case 14: Result = “Fourteen”

Case 15: Result = “Fifteen”

Case 16: Result = “Sixteen”

Case 17: Result = “Seventeen”

Case 18: Result = “Eighteen”

Case 19: Result = “Nineteen”

Case Else

End Select

Else ‘ If value between 20-99…

Select Case Val(Left(TensText, 1))

Case 2: Result = “Twenty ”

Case 3: Result = “Thirty ”

Case 4: Result = “Forty ”

Case 5: Result = “Fifty ”

Case 6: Result = “Sixty ”

Case 7: Result = “Seventy ”

Case 8: Result = “Eighty ”

Case 9: Result = “Ninety ”

Case Else

End Select

Result = Result & GetDigit _

(Right(TensText, 1)) ‘ Retrieve ones place.

End If

GetTens = Result

End Function

‘ Converts a number from 1 to 9 into text.

Function GetDigit(Digit)

Select Case Val(Digit)

Case 1: GetDigit = “One”

Case 2: GetDigit = “Two”

Case 3: GetDigit = “Three”

Case 4: GetDigit = “Four”

Case 5: GetDigit = “Five”

Case 6: GetDigit = “Six”

Case 7: GetDigit = “Seven”

Case 8: GetDigit = “Eight”

Case 9: GetDigit = “Nine”

Case Else: GetDigit = “”

End Select

End Function

Hi,

it is not necessary to use SELECT….

Dear sir

Thanks for guidance

I need you’re created excel copy

Pls send me mail of ready excel

Thanks

Vijay

Really enjoy watching how you explain step by step

I was looking for a invoice in excel please help me to develop

Sir,

Can you please provide Excel invoice soft copy.

Sir,

How we restricted user to enter if wrong category into input box.

Email address

Your video is good for beginners

SIR ,

HOW TO MAKE MAKE A CUSTOMIZED BILL PRINT

SIZE LIKE 80*297MM

OR

YOU CAN GIVE A LINK TO MY MAIL IF YOU ALREADY MAKE THAT

you are awesome

please send it by email

Hello Dinesh,

Could you please send me a copy of this Excel document? I don’t see where I can download it on your website. This is so helpful!

Thanks,

Matthew

range(“A9″)=txtnameaddress&””&””GSTIN”&”-“&txtGSTIN this line come syntax error come plz give any solution for this

Dear sir

Thanks for guidance

I need youâ€™re created excel copy

Pls send me mail of ready excel

Thanks

Best Regards

Lokenath Saha

Bangladesh