Mark Moore - Mastering Excel: User Forms

Mastering Excel

User Forms

Mark Moore

Acknowledgments

I would like to thank Sharon Deitch for her invaluable help in testing, proofreading and making this information as clear as possible. I tend to skip steps here and there. Sharon keeps me in line.

Copyright 2015 by Mark Moore. All rights reserved worldwide. No part of this publication may be replicated, redistributed, or given away in any form without the prior written consent of the author/publisher or the terms relayed to you herein.

User Interface

Why should you use a form? Because it gives you more control over data collection. By using Excels built-in form objects, you can limit what the user can input. Forms also give you an alternate way to display options the user can make. Your Excel model can then react to the choices made by the user. Without forms, user inputs are limited to typing in numbers or text in a cell. Using forms gives the user a richer experience.

You can download the follow along workbook at my website: http://markmoorebooks.com/user-forms/

You will need to sign up and the click on the link in the confirmation email. Then you will receive an email with the workbooks attached. Emails might get caught in your Spam folder so make sure you check it if you dont get the email within a few minutes.

Adding Forms

All the form objects are located on the Developer tab.

If you do not have the Developer tab visible, do the following (in Excel 2013):

1 On the File tab, click Options.

3 In the Excel Options dialog box, click on Customize Ribbon.

4 - In the window on the right, select Developer.

To show the Developer tab in Excel 2007:

1. Click the Office button, and then click Excel Options.
2. Click Popular at the top of the left window, and then click Show Developer Tab in the

Ribbon.

3. Click Insert, and you will see this box appear in the Ribbon:

Form Controls vs. ActiveX Controls

Youll notice that the drop-down box has two sections, Form Controls and ActiveX controls.

Form Controls are built into Excel. They are backwards compatible to Excel 5. You can use form controls with or without macros. They are simple to use and can meet most of regular Excel users needs.

ActiveX Controls are based on the ActiveX technology (developed by Microsoft). These controls are loaded by Excel and have much more flexibility than the Form Controls (you can control just about anything on an ActiveX Control, font style, font color, etc.). However, ActiveX Controls have a few drawbacks. Some ActiveX Controls cannot be used directly on a worksheet but rather need a custom form (we will cover this later). Many computers will not allow ActiveX to run natively, so you will have to add the file to the Trusted Sites (even then the IT department might not allow it). Lastly, they can be unstable and crash unexpectedly.

Therefore, for the rest of the lesson, you will be using Form Controls.

Using Forms

To really understand how to use forms, you are going to use the sample data in the follow along workbook to build several mini-dashboards. Each dashboard uses one form component, so you can get experience with each object. I build dashboards a particular way that leverages Excels ability to extract data from a database. I explained this topic in detail in the Mastering Excel: Pivot Tables lesson. If you have not read that lesson or its been a while since you read it, heres a quick review.

Excel Model Data Structure

To leverage your time and make your Excel model as flexible as possible, you should think of building your model with three distinct layers.

Data Layer

This is the foundation of your reports. This is an extract from Great Plains, Oracle, SAP, or whatever system you use. Each field needs to have a title and must contain similar data. For example, if the column has a title of Name, the entire column needs to have names in it. Dont stick someones employee ID in there. Add another column for Employee ID. It makes no sense to save a column. Excel doesnt care and you have 16,000 columns, use as many as you need.

The data will end at the first completely empty row and the first completely empty column. Do not add empty rows to make the data more readable. Data is not supposed to be readable. It is simply used to store random facts. Your reports are what needs to be readable.

Here is a screen shot of a sample data layer:

Report Layer

This is the layer that everyone sees. It has the charts, Pivot Table, reports, pretty colors, logos, etc. You make this one shine and look great. This is the layer that gets printed and displayed in presentations.

Business Logic

This is what ties together the Data layer and the Report layer. These are the formulas, charts, and other calculations that extract data from the data layer and convert it into information.

The layers are what is called loosely coupled. This means that although they communicate with each other, you can change one layer without affecting the other. For example, you wont have to rebuild your entire model when a new field is added to the data layer.

How do forms fit in? Forms live in the Report layer. They allow your users to have some decision-making capabilities to change the information. For example, if you built a report that showed January results that would only work for a person that wanted to see January data. You would have to build another report to show February data. At the end of the year you would end up with 12 reports. Instead, I suggest you build a report that allows the users to choose which month they want to see. One report now can be sent to 12 people, each of whom wants to see a different month. With a little bit of advance planning, you have increased the efficiency of this report 12 times over. Make sense?

Button

The button is the simplest of form objects to use. The principal function of the button control is to run a macro by clicking the button.

Even though this lesson does not go into macros, you can create a button control to see how it works.

You can download the follow along workbook at my website: http://markmoorebooks.com/user-forms/

1 - Open the Forms.xlsx follow along workbook.

2 - Select the Button worksheet

3 On the Developer tab, click Insert, and then click the button icon under Form Controls.

Your mouse icon will change to a small cross-hair.

6 - Click and drag the cross-hair to create a button

When you release the mouse, the Assign macro dialog box appears.

If you had a macro recorded in this workbook, you could assign it to this button. Then all you need to do to run the macro is click the button.

7 - Even though you dont have a macro, click OK

This is how my button looks: