Free Spreadsheet for Using Forms with Excel

by bobroan on July 29, 2013

If you’re using Excel as a database, forms can make your life a lot easier. With a form, you only see the record with which you’re working, forms have a much better user interface than the spreadsheet grid (what doesn’t?) and you don’t have to keep scrolling if you have more columns than fit on a screen.

formCreator.xls is an Excel spreadsheet you can use to create a form for your excel database that contains whatever and how many columns you want.

The form also copies some formatting from your excel data.  It will use the same font and background colors and if you’ve got a dropdown box for a cell or some other kinds of data restrictions, your form will have them, too.

The form only gets that information once, when it’s being created, and takes it from row 2.  The formatting and data validation only matters to the form if it’s on row 2 of your data.

Getting Started

In order to keep the references to the test data from getting confused during the download, download these files in this order

First, download testdata.xls, and

Then download formCreator.xls

Save them both.

If they don’t open on your computer, open testdata.xls first and then formcreator.xls, in that order.

Testdata.xls is a sample spreadsheet database for which we’ll create a form. (In “real” life, you’ll use the spreadsheet containing your excel database in its place.)

FormCreator.xls is the spreadsheet which will create the form.  You’ll use cell references to the headers of the columns in your data sheet and from that, the form knows where to look for all of its data.

The data for which we’ll create our form is in Testdata.xls and looks like this
data01

data01a

data01b

Normally, the headers in row 1 would describe real characteristics like names, birthdates, phones et al, but I’ve used the headers to assist me in this article by using them to describe the restrictions on the excel data in their columns.

Columns A through F have restrictions on what can be put in them.

I included some values in cells that won’t meet the data restrictions to use as examples later.  Excel let me do this because only the cells in row 2 have the data restrictions and it doesn’t matter what’s in the other rows when we’re using the form.

Notice that only the second row has the different font and background colors.

Here’s how these restrictions look in excel.

validationWholeNumber

Cell A2 is restricted to whole numbers (no decimals) between 1 and 5.  Blank values are allowed.  The first item of the form will always have that restriction, no matter what the record

 

 

 

 

 

 
validationDecimal
Cell B2 is more loosely restricted to decimal numbers between 1 and 7.  Blank values are allowed.  The second item of the form will always have this restriction

 

 

 

 

 

validationList
Cell C2 is limited to either Item 1, Item 2 or Item 3.  Blank values are allowed.  The form’s third item will have this restriction
 

 

 

 

 

validationDates

 

 

 

 

Cell D2 is limited to dates between 1/1/2013 and 6/1/2013.  Blank values are allowed. The form’s fourth item will have this restriction

 

 

 

 

validationTextLength

 

 

Cell E2 must be between 2 and 6 characters long.  Blank values are allowed.  The form’s fifth item will have this restriction

 

 

 

 

validationLogical

 

 

Column F must be true or false.  The form’s sixth item will have this restriction

 
 
 

 

Creating the Form

“formCreator.xls” looks like this:

configuration01

 

 

 

Column A contains the columns you want included on your form and Column B contains some formatting options for the form.

Notice that we didn’t include column J (green circle) and we switched the order of columns Q and R (red circle.)

Starting in row 2, Column A contains cell references to the headers of columns in the “testdata.xls” data file which you downloaded.  For example, the blue circle shows that cell A5 contains the contents of cell D1 on the ‘thetestdata” worksheet in the ‘testdata.xls’ file

You must use pointers/formulas instead of just typing the headers because the form creator uses those formulas to know from where to pull its data.

To use this form for yourself, just open up the spreadsheet with your excel database and change column A to references to your spreadsheet.  But wait until you’re finished reading this.

Cells B2 through B5 format your form.

  • Your form has columns.  Cell B2 lets you specify how many rows will be in each column
  • The form will use the terms from column A as labels for the different fields.  Cell B3 lets you specify how wide those labels are,
  • Cell B4 lets you specify how wide the actual data controls are, and
  • The background color of cell B5 will be the background color of your form.

To create and start using the form:

To make sure that what you see continues to match what’s show in this article, click anywhere in Row 5 of the formCreator.xls workbook.  That way the form you’re about to see will start with data from row 5, just like this article.

Of course, during normal use, you can start on whatever row makes sense for you.

Select the view tab and then press the Macros button.

 

macroStart02 macroStart02b

 

If necessary, choose “view macros” from the drop down menu, and then

 

 

 

 

Make sure “open_data_entry_form” is selected and press the “run” button.

 

 

 

 

 

 

The data entry form looks like this (If you have a lot of things open on your desktop, it may appear behind something and require you to click on the excel document formCreator.xls to bring it to the front):

 

form01

 

 

Each column contains a maximum of 11 rows, as specified on the configuration sheet and the form’s background color is the same as that in cell B5 of the configuration sheet.  The labels and data boxes are also the sizes specified there.

The font and background colors match those in row 2 of the testdata.xls spreadsheet

There is no column J and the order of Q and R has been switched.

The heading in at the top center tells us which row we’re working with and there are a set of buttons along the bottom to let us move around.

 

 

“previous row” moves up a row,

“next row” moves down a row,

“go to row” lets you jump to a row

“new row” adds a row at the bottom of the data sheet, and

“done” closes the form

 

form02

 

 

The list for column C has been carried over to the form and the logical nature of column F has been converted to a checkbox.

 

 

The other data restrictions also carry over and will all be checked when we press one of the buttons to move to another row.

Let’s try changing the whole number field to a decimal and the decimal field to a value outside the limits.

form03

 

 

Change the value in the first box from 4 to 4.1 and in the second from 3.4 to 8.

 

 

 

When we press the “previous row” button, we get the message

form04

The longer, elaborated column names makes this a bit confusing.  The first part of line 1 (wholenumber between 2 and 5) is the column header.  So it’s saying that the first item must be a whole number between 2 and 5)

 

 

 

Let’s correct those fields and then change columns H, I and K, which will allow any information.

 

 

 

The form now looks like this:

 

form05

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After we press the “previous row” button, the data sheet now contains the new information

form06

 

 

 

 

Here is the information for row 4 and we can see that the “list” value is not an allowable item

 

form07

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When we press the “previous row” button, we get this message:

form08

 

 

 

 

 

 

 

 

 

 

 

After we select an item from the drop down list, we can move to the previous row (3).  The form now contains invalid data for “length” column and the logical data column (the checkbox is grey, not clear or black):

 

form09

 

 

 

 

 

 

 

 

 

 

When we try to move to the previous row (2) we get this message:

 

form10

 

 

 

 

 

 

 

 

 

 

 

When we correct that information and press the “previous row” button, we move to row 2 and see:

 

form11a

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If you try pressing the “previous row” button, you’ll get the message

 

form11b

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If you fix the whole number and press the “previous row” button again, you’ll get this message because row 1 contains the headers and can’t be changed:

 

form11

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If we respond yes, it determines that row 6 is the next empty row and moves there.  The form is empty:

 

form12

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

We’ll enter information in some fields:

 

form13

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And then click the “done” button.  The new information is on row 6 of the spreadsheet.  Blank values get past the data validation check for columns A and D because the data validation set up allows for blank values.

 

data02

 

 

 

 

 

Now that you’ve seen how this form works with an Excel database, just change the cells in Column A to point to your excel database and see how much easier data entry can be!

 

 

 

 

 

Leave a Comment

Previous post: