Home > Excel Tutorial > Creating Invoice & Entering Data Quickly Through Userforms

Creating Invoice & Entering Data Quickly Through Userforms

In this invoice template ,the userforms that contains textbox,listbox and buttons are used to enter quickly clients data and products data.

  • Ago,requested data can be searched in lists through the text boxes .
  • Then, when the list item is double-clicked or when the enter key is pressed, the item’s data is entered in the sheet.
  • Also, flashing cell feature was used in template to remind entering the product quantity . Used codes for this:

Public RunWhen As Double

Sub StartBlink()
Dim cell As Range
For Each cell In Sheets(“Invoice”).Range(“B21:B35”)
If cell <> “” And cell.Offset(0, 1) = “” Then
If cell.Offset(0, 1).Interior.ColorIndex = 3 Then ‘ Red Text
cell.Offset(0, 1).Interior.ColorIndex = 2 ‘ White Text
Else
cell.Offset(0, 1).Interior.ColorIndex = 3 ‘ Red Text
End If
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, “‘” & ThisWorkbook.Name & “‘!StartBlink”, , True
Else
cell.Offset(0, 1).Interior.ColorIndex = xlColorIndexAutomatic
End If
Next
End Sub

Sub StopBlink()
ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
Application.OnTime RunWhen, “‘” & ThisWorkbook.Name & “‘!StartBlink”, , False
End Sub

  • The used formulas in worksheet are protected by the following codes :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$G$21” Then
Target.Formula = “=E21*F21”
ElseIf Target.Address = “$G$22” Then
Target.Formula = “=E22*F22”
ElseIf Target.Address = “$G$23” Then
Target.Formula = “=E23*F23”

….

Target.Formula = “=E34*F34”
ElseIf Target.Address = “$G$35” Then
Target.Formula = “=E35*F35”
ElseIf Target.Address = “$G$36” Then
Target.Formula = “=SUM(G21:G35)”
ElseIf Target.Address = “$G$41” Then
Target.Formula = “=SUM(G36:G39)”
End If

End Sub

  • The created invoice can be copied to selected record sheet.

Download Example File

Advertisements
  1. enkh sharav
    March 15, 2017 at 1:22 am

    thank you

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: