In this article we’re going to cover how to create a user input form step-by-step, with data inputted and automatically saved within the file along with a reporting dashboard. In this short space we can only really cover how to build a basic form, but all the important Visual Basic code, etc is included and hopefully this can serve as a template to set you off to build fantastic user forms and do much, much more with this.

 

For clarity, this article isn’t covering the in-built Excel userforms, instead we’re going to build one ourselves from scratch. The in-built version uses a very limited set of controls with virtually no scope for resizing, changing fonts, colours and backgrounds, etc so you simply cannot build anything that doesn’t look like a bog-standard boring input form. If you do want to explore them in more detail, hit Alt + F11 to bring up the VBA screen, go to Insert – UserForm and have a play with it.

 

For our simple example we’ll create an input form with just 5 fields, designed to input the winner of the Sports Personality of the Year award (UK) for each year.

 

STEP 1 – Decide which fields you’re using and enter them into a new sheet called ‘Form’. Also create blank sheets called ‘Data’ and ‘Dashboard’. On the Form sheet, add an additional unique ID field (which you will hide later). In this particular example ‘Year’ could be used as the unique reference, but it’s good practice to get into the habit of always creating one.

 

 

STEP 2 – Add data validation to the fields as necessary. In this case Year must be a Year format, DOB a date format and ‘Sport’ and ‘Nationality’ are both based on drop-down lists.

 

STEP 3 – Unlock the cells which the users will input to, as we’re going to protect the rest of the form later. Right-click on the relevant cells, Format Cells, Protection and uncheck ‘Locked’.

 

STEP 4 – Give each field a range name. This is good practice in case you need to move fields around later on. You don’t want to be referring to ‘C5’ in your code when you can refer to it as ‘FieldYear’ instead. If you need to add a new field or re-order your field and it needs to move to a different cell, it won’t matter because the code will still be looking for its range name, ‘FieldYear’.

 

 

STEP 5 – Some general formatting to tidy it up. In this example, I’ve added a title, removed gridlines (Alt, W, V, G), hidden row and column headings (Alt, W, V, H), added a background colour, added new rows between fields, adjusted some row heights / column widths and adjusted the print settings so it fits nicely to one page landscape. You can do much more to customise the look of your form, but for now let’s keep it simple.

 

 

STEP 6 – Go to the Data sheet and format it so it is ready to become the place the records are saved. Once you’ve manually entered the headers (see Row 6 below), click Ctrl + T to save it as a table and give the table a named range – in this case TableWinners.

 

 

Now this is created you know we want to start pasting entries from Row 7 downwards so you can add the formula for Unique ID on the Form in cell C4:

=SUBTOTAL(3,Data!B7:B1048576)+1

The Unique ID will increment every time a new entry is added. You can hide Row 4 in the Form sheet too so Unique ID isn’t visible, users don’t need to know it’s there.

 

STEP 7 – Add formulas on the Data sheet which lookup the field ranges on the Form sheet to row 5. This will be used when a new entry is submitted as the row to copy from. Hide Row 5 in the Data sheet as you don’t need users to see it. E.g. the formula in D5 would be =FieldName

 

 

 

STEP 8 – Clear code. Write a VBA module which clears the current entry. This will need to happen after a new entry has been added. Alt + F11 opens the Visual Basic window, go to Insert – Module and below is the code you need. Bear in mind now you will need to save the file as Excel Macro-Enabled Workbook, .xlsm file type.

 

Public Sub clear()
   
    Application.ScreenUpdating = False
    Sheets(“Form”).Activate
    ActiveSheet.Unprotect
   
    Range(“FieldYear”).Value = “”
    Range(“FieldName”).Value = “”
    Range(“FieldDOB”).Value = “”
    Range(“FieldSport”).Value = “”
    Range(“FieldNationality”).Value = “”

    ActiveSheet.Protect
    Application.ScreenUpdating = True
    ActiveWindow.ScrollRow = 1

End Sub

 

STEP 9 – Save button. Write another VBA module which copies the information on the Form sheet to a new row in the table and clears the form (you can copy the below routine).

 

Public Sub save()

    Dim DatabaseRowCount As Integer
    Dim PasteRow As Integer
   
    Application.ScreenUpdating = False
   
    ‘Check mandatory fields have data
    If Range(“FieldYear”) = “” Or Range(“FieldName”) = “” Or Range(“FieldDOB”) = “” Or Range(“FieldSport”) = “” Or Range(“FieldNationality”) = “” Then
        MsgBox “Please complete all mandatory fields first”
        Exit Sub
    End If

    ‘Find the last row
    DatabaseRowCount = Range(“TableWinners”).Rows.Count
    PasteRow = DatabaseRowCount + 7
    
    Sheets(“Data”).Activate
    ActiveSheet.Unprotect
   
    ‘Unfilter
    With ActiveSheet
        If .FilterMode = True Then .ShowAllData
    End With
   
    ‘Copy
    Range(“FieldCopyStart”).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
   
    ‘Paste
    ActiveSheet.Cells(PasteRow, 2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True
    ActiveWindow.ScrollRow = 1
   
    clear
   
    Application.ScreenUpdating = True
   
    ‘Update dashboard
    ThisWorkbook.RefreshAll
    Sheets(“Dashboard”).Activate
   
    ‘Save
    ActiveWorkbook.save
   
End Sub

 

Add a ‘Save’ button and set it to run the save macro when clicked. Again, I prefer to use autoshapes rather than the in-built Button from Form Controls. Go to Insert – Shapes and select a Rectangle – Rounded Corners and you can format colours and borders as you wish. Right-click the object, go to Assign Macro and choose the macro named ‘save’.

 

 

STEP 10 – Create a new sheet called ‘Pivot’ and add a pivot table of the Data listing in there. You can press Alt, D, P and choose Microsoft Excel list or database and use ‘TableWinners’ as the range. Create the below pivot table, being sure to sort the Count of Unique ID descending.

 

 

STEP 11 – Summarise the results in your Dashboard sheet. I won’t get into anything fancy here but you can visit our Dashboard Tricks section to give you some inspiration for that.

 

 

This is just a very basic summary with a slicer to select Nation but you can add all sorts – list boxes, combo boxes, scrollbars, action buttons which run more enhanced code like sending automatic emails and all sorts. If you want to explore any of those in more detail get in touch with us.

 

If you find any technical faults with this, or have any general feedback, please leave a comment.