session 8 - Excel Forms and Validation

Creating a Simple Form

Today we will look at how to create a simple Excel data form for entering information into a spreadsheet. A data form can be created using the existing column headings in a table or cell range.

For this example, I am using the same spreadsheet as last week - 2015PivotTable.xlsx

Before we can create a form, we need to add the Forms Button to the Quick Access Toolbar. Click on the little arrow at the end of the quick access toolbar to display the customise menu.

Click on More Commands, then All Commands. Locate the Forms Button and add it to the toolbar.

Highlight the table (A3:G29) and click on your newly created Form button.

Are you impressed? Excel should have created a nice data entry form for your table. You can even use this to scroll through the existing data in your table.

If you have a blank "template" or partially completed spreadsheet, you can still create a form. In our example, lets say you have the names and branches, but no data. You can create the form so that the missing data can be entered easily.

You will see that now the form has blank data - the user can enter the Q1 - Q4 figures and it will calculate the total as you go. Use the navigation buttons to move from one row to the next.

You could name the sheet to create an even more meaningful form as it would have a relevant title rather than just Sheet1

EXERCISE 1

Create a new spreadsheet to keep a list of all of your books. Have a title at the top - My Book Library

Include the following headings:
Title
Author
Edition details
Date acquired
Cost
Rating

Enter your first book straight into the spreadsheet.

With your cursor on the first item, create a form

Use this form to add another 5 or so books to the table. If you don't have the exact information handy, just make it up.

Creating a User Form

Use the 2015PivotTable.xlsx file.

Start by Loading VBA. To create the form, you need to go to Insert / Userform

Now to create our form.

Start by adding a text box for the Sales Rep.

Then add a label to create a heading for the text box.

Next add a combo box to select the Branch.

Add text boxes for the quarterly sales data to be entered

Name them Quarter1, Quarter2, Quarter3, Quarter4 and create corresponding labels.

Add 3 Command Buttons

OK - Named OKButton
Clear Form - Named ClearButton
Cancel - Named CancelButton

Add a command button to your spreadsheet that will allow the new form to be opened.

 

SAVE the macro that you have just created. You may be prompted to save your spreadsheet as a Macro enabled spreadsheet - select the file type when prompted to Save As.

 We now need to create a couple of simple macros to be able to use the form properly and allow the data to go into our spreadsheet.

The first one is to initialise the form

 

**************************************

Private Sub UserForm_Initialize()

'empty any previous values
SalesRep.Value = ""
Quarter1.Value = ""
Quarter2.Value = ""
Quarter3.Value = ""
Quarter4.Value = ""

'Empty the list box
Branch.Clear

'Add the items to the combo box
With Branch
    .AddItem "Cranbourne"
    .AddItem "Dandenong"
    .AddItem "Frankston"
End With

'Put cursor in first text box
SalesRep.SetFocus

End Sub
***********************************
Add the data to the spreadsheet - this will occur when you have entered the data onto the form and click the OK button.

To put the code in, double-click the OK button on the form.

*************************************
Private Sub OKButton_Click()
Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'Determine emptyRow
emptyRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'Transfer information
Cells(emptyRow, 1).Value = SalesRep.Value
Cells(emptyRow, 2).Value = Branch.Value
Cells(emptyRow, 3).Value = Val(Quarter1.Value)
Cells(emptyRow, 4).Value = Val(Quarter2.Value)
Cells(emptyRow, 5).Value = Val(Quarter3.Value)
Cells(emptyRow, 6).Value = Val(Quarter4.Value)

End Sub
********************************************

You will need to place code in for the other 2 buttons as follows:

***********************************
Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub ClearButton_Click()
Call UserForm_Initialize
End Sub
******************************************

EXERCISE 2

Create a UserForm for Exercise 1