VBA Case Study #5 – Making a User-Friendly Invoice Generator with Input Forms

[IMAGE] Invoice Generator with User Forms for Data Input

Summary: Macros often add efficiency that’s impossible with manual Excel processes. In Case Study #4 we looked at how I turned a Excel job that took 37 hours into one that was done in less than 15 minutes. Sometimes, though, efficiency alone is not enough.

After I built the Invoice Generator macro in the previous case study, I learned that not all its users knew how to use Excel. Have you ever run across such users…folks to whom entering data into a spreadsheet is as foreign as piloting a space shuttle is to you and me?

Though the Invoice Generator’s users could open a workbook, they couldn’t do much more. This provided me with an opportunity to make the lives of the Pro PowerWashing team even easier! I did it with User Forms.

Difficulty: Intermediate

Download the Sample Workbook

[Image] Download Workbook Download the sample file with VBA code
Invoice-Generator-With-Forms (1,228 Kb)

#1 – Why did I create the Macro?

» Back to contents

Most members of the Pro PowerWashing team had no previous Excel experience before they began using the Invoice Generator macro. For that reason, they asked how I could make the workbook easier for their team.

I first reminded myself that the workbook contains the following client data:

  • Name
  • Address
  • Email
  • Phone

And this invoicing data:

  • Date
  • Invoicing Number
  • Client Name
  • Service
  • Price

Users had to manually input each of these data points, but they didn’t want to learn how to use a spreadsheet.

After a few conversations with the Pro PowerWashing team, I learned that they were familiar with entering data into website forms. I decided to mimic such forms in VBA to provide them a user-friendly way to enter client and invoicing data.

If I automated this data-entry process so that users would see a form instead of the “Invoicing” sheet, it would also mean they wouldn’t see the last invoice number used. This is a problem because employees wouldn’t be able to determine what invoice number to use next in sequence if they couldn’t see the last invoice number on the “Invoicing” sheet.

Therefore, I also decided to automate the process of generating invoice numbers. The easier the better, right?

The version of the macro created in Case Study #4 saved significant time and money, but the changes I was about to make would allow all members of the Pro PowerWashing team to use this impressive tool!

#2 – What does it do?

» Back to contents

Pro PowerWashing was manually updating the following table on the ”Clients” sheet each time they added a client:

They then updated the following table on the ”Invoicing” sheet every time they performed a power washing job:

As I mentioned in Case Study #4, the Invoice Generator macro uses the ”Invoice Sent” column to tell whether the service in that row has previously been invoiced.

Since the macro uses the data in the above tables, I didn’t want to modify the table structure. Instead, I wanted to create front end forms into which users could input the data. I did so via the following two forms — one each for client and invoicing data:

I also wrote VBA code to automatically determine the invoice number based on the last number used. You can see one of the auto-generated invoice numbers in the ”Invoice Number” field above.

#3 – How is it better than using Excel without the enhanced VBA code?

» Back to contents

Although the initial macro saved Pro PowerWashing about $500 per month, the new enhancements allowed their entire team to be able use the workbook.

I’ve worked with several clients who have never, if at all, used Excel for even the simplest of tasks. Manually entering data into spreadsheets may seem simple, but for these folks it’s a hurdle they need help to overcome.

When I’ve given clients workbooks with user-friendly forms, they’ve often told me how much easier the forms have made their lives. That was the case with Pro PowerWashing, too.

#4 –How does the code work?

» Back to contents

It’s time to dig into the forms and code that made all this possible!

User Forms

Before writing the VBA code, I had to build UserForms into which users would enter client and invoicing data. Although forms can look complex, basic forms aren’t too difficult to build.

From the VBA editor, I inserted each UserForm using the “Insert” menu:

Doing so inserted the following form:

I then modified the form’s BackColor and Caption (the title displayed at the top of the form) using the properties pane on the lower left side of the VBA editor window:

I inserted a frame from the Toolbox to set the text boxes apart from the rest of the form.

I even changed the frame’s caption to accurately represent the purpose of the form.

After I finished with the frame, I inserted a text box for each relevant piece of data (e.g., since the Client table included four columns, I inserted four text boxes). I inserted the text boxes and their labels using the Toolbox options circled below:

At the bottom of the form I added two buttons – one to save the entered data to the relevant sheet (i.e., “Clients” or “Invoicing”), and one to return to the “Main Menu” tab. I changed the button captions via the properties pane.

VBA Behind the UserForms

Each form’s primary purpose is to write data to the relevant Excel sheets. Therefore, I needed code to write each entered value to the correct sheet.

For example, here’s the code to write data to the “Clients” sheet. It’s activated by clicking on the “Save” button and accessed by double-clicking on the form button in the VBA editor.

Option Explicit

Private Sub CommandButton1_Click()

LastRowofData = _
Worksheets("Clients").Cells(Worksheets("Clients").Rows.Count, "A").End(xlUp).Row

WriteRow = LastRowofData + 1

Worksheets("Clients").Cells(WriteRow, 1).Value = TextBox1.Text
Worksheets("Clients").Cells(WriteRow, 2).Value = TextBox2.Text
Worksheets("Clients").Cells(WriteRow, 3).Value = TextBox3.Text
Worksheets("Clients").Cells(WriteRow, 4).Value = TextBox4.Text

TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""


End Sub

As you can see, it runs on the button’s Click event.

The code first finds the last row of data on the “Clients” sheet. It then adds one to that row to identify the first empty row. The first empty row is the row to which it’ll add the new client.

The VBA then writes the value of each text box to the appropriate cell in the workbook. After it’s finished, it clears the value of every text box by making each text box blank (specified by the double quotation marks).

And that’s it! Basic code to write values from a form to a worksheet isn’t too difficult, right? The code behind the “Main Menu” button is even easier:

Private Sub CommandButton2_Click()

Worksheets("Main Menu").Activate
Unload Me

End Sub

The first line of code activates the “Main Menu” worksheet, and the second line (“Unload Me”) closes the form and clears any data in it.

Main Menu

The “Main Menu” sheet serves a critical purpose, in that it allows users to access both UserForms via buttons.

I added each button, while on the “Main Menu” sheet, via the “Insert” menu on the “Developer” tab:

I changed the label of each button by right-clicking the button and selecting “Edit Text”. I then wrote a macro for each button by right-clicking the button and selecting “Assign Macro”. Doing so brings up the following window:

I clicked the “Edit” button to bring up the appropriate section of code in the VBA editor.

Here’s the code I wrote, via the editor, to open one of the UserForms:

Sub MainMenu_Button1_Click()

UserForm1.Show

End Sub

As you can see, opening a form is as simple as adding the “.Show” command to the name of the form!

VBA to Calculate Invoice Numbers

To automatically determine the appropriate invoice number, the following code runs when the “Invoice” form initializes (i.e., opens):

Private Sub UserForm_Initialize()

LastRowofData = _
Worksheets("Invoicing").Cells(Worksheets("Invoicing").Rows.Count, "A").End(xlUp).Row

TextBox2.Text = Worksheets("Invoicing").Cells(LastRowofData, 2) + 1

End Sub

This snippet first finds the last row of data in the “Invoicing” sheet. It then sets the value of the second text box (the one for the “Invoice” number) equal to the last invoice number plus one.

It’s worth noting that, on this form, I included the following check box:

If a user will be entering a service for the same invoice number that they just used, they’ll check this box before clicking the “Submit for Invoicing” button. Checking the box allows the following code to work when they click the “Submit for invoicing” button:

If CheckBox1.Value = True Then
Worksheets("Invoicing").Cells(WriteRow, 1).Value = TextBox1.Text
Worksheets("Invoicing").Cells(WriteRow, 2).Value = TextBox2.Text
Worksheets("Invoicing").Cells(WriteRow, 3).Value = TextBox3.Text
Worksheets("Invoicing").Cells(WriteRow, 4).Value = TextBox4.Text
Worksheets("Invoicing").Cells(WriteRow, 5).Value = TextBox5.Text

TextBox1.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
CheckBox1.Value = False

Else
Worksheets("Invoicing").Cells(WriteRow, 1).Value = TextBox1.Text
Worksheets("Invoicing").Cells(WriteRow, 2).Value = TextBox2.Text
Worksheets("Invoicing").Cells(WriteRow, 3).Value = TextBox3.Text
Worksheets("Invoicing").Cells(WriteRow, 4).Value = TextBox4.Text
Worksheets("Invoicing").Cells(WriteRow, 5).Value = TextBox5.Text

nextInvoice = TextBox2.Text + 1
TextBox2.Text = nextInvoice

TextBox1.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""

End If

Although this code closely resembles the code behind the “Save” button, it contains additional code, via a simple “If” statement, that checks the value of the CheckBox.

If the CheckBox is true (i.e., checked), the invoice number will not change. If the CheckBox is false, the invoice number will increase by one. The only other time the code adds one to the invoice number is when the form opens.

#5 – Summary of key learning points

» Back to contents

I focused this case study on the VBA required to build the solution, but it’s important to emphasize that I couldn’t have created it without taking the time to listen to my end users.

As VBA developers, we often come up with solutions that seem great to us. However, such solutions aren’t always the best solutions for our clients.

User forms are a great way to satisfy the usability needs of Excel users. As you’ve seen, although forms are relatively easy to implement, they’re a key component to building user-friendly solutions.

Never assume that your macro is finished without first getting the opinion of your end user(s). Nothing is more satisfying than building a solution that the end users enjoy using!

#6 – About the Author

» Back to contents

Chris Cox – Two decades ago, Chris taught himself VBA to help him eliminate annoying manual Excel tasks. Twenty years later, Chris has turned that experience into a second career as a highly-rated independent consultant specializing in Excel and Excel automation. He loves exceeding client expectations by making their processes as efficient as possible!

Chris is also passionate about writing, editing, and proofreading, and he’s successfully done all three both in full-time employment and as a consultant.

Chris currently works as a full-time as a Senior IT analyst for a global asset management firm. He has also worked in government, healthcare, and consulting. He writes fiction in his spare time.

If you’re interested in working with Chris, please reach out to him directly at [email protected] or find him on his Upwork page.

Victor Chan
Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recommended Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.