Unless you love compiling reams of paper, matching questionnaires with participants, deciphering answers, and inputting data into excel sheets cell by cell, then you may want to consider using Excel User forms for your next multiple choice questionnaire. A userform, like the one below, will record the participant number and answers and place the data in a worksheet, all nicely packaged and ready for analysis.
It is not hard to do, however it is fiddly and time consuming. The cost benefit of using a userform becomes heavily weighted in its favour the more participants you have. You will be in the black (in time and effort) for anything more than say 20 – 25 participants, compared to using paper questionnaire (with number of items > 5). This is not counting the skills learned when making one, which can be the basis of making others and saving even more time.
The guide below makes a form with around 6 items. If you have any more, you will need to use more than one userform, and link them together. This is not difficult, but the instructions are not included here.
If this is not daunting, then put aside a couple of hours, grab a cup of tea, and let’s get started!
Note: these guidelines written using Microsoft Excel for Mac 2011 (Microsoft Visual Basic Version 14.2.5).
PART ONE – THE FORM APPEARANCE
1. Open an Excel Workbook.
2. Make sure the Developer tab is showing. If not go to the little cog at the right end of the ribbon and click on the little arrow down. Choose ‘Ribbon preferences…’. Scroll down in the ‘Tab or Group Title’ window until you see ‘Developer’. Click in the box for it to show.
3. In the Developer tab, click on Editor. Depending on if you have used the editor before a ‘Project’ window will appear. You should see just one folder: Microsoft Excel Objects. The worksheet you have opened is in this folder. When you create a form it will live in the ‘forms’ folder.
4. Click on Insert in the top menu, and click on UserForm. Two extra windows will appear. The first larger one with a grey box is the userform template. The second is the toolbox, which contains objects you can drag onto your user form. You will also notice that the Project window now shows a second folder: ‘Forms’ containing UserForm1. At this point the userform (grey box) is quite small. You will need to open another window in order to change the characteristics of UserForm1 and its objects.
5. Click on View in the top menu, and click on ‘Properties Window’. The window ‘Properties’ will appear. This window will tell you the properties of any object you click on and make active. If you click on the UserForm it will show you its properties. If you click on objects within the UserForm, it will show you the objects’ properties. You can change properties using the window. The first thing to change is the size. On the top of the Properties window is a tab which allows you to toggle views between Alphabetical and Categorised. I find Categorised is easier. Choose this.
6. Change Height to 750 and Width to 800 (or whatever you prefer). While you are there, change the name of the userform to Questionnaire1.
7. Now for the fun part, using the toolbox. Click on Label in the toolbox, and draw a box on the top of your userform. Click in it and type the title of you form…say ‘Example Questionnaire’. It is easy to change later, just by clicking on it and typing.
8. You will notice that the properties box is now showing the properties of that Label. This is where you can change the size and format of the text. Click on the Font part of the properties window and a little box will appear to the right, click on this to show the formatting options. Change the font size to something legible! A 18 size would be just great. You can also click on the text align and choose centre instead of left.
9. It is really important to label all your objects including your Label Boxes. The default is Label1. Change this to LabelTitle.
10. In the toolbox, choose Option button and place one on the userform. In its Property window remove ‘OptionButton 1’ in the caption section. Drag the box in which the option button sits, to a small size. Then copy and paste this four more times so you have five option buttons in a row. Drag them all into place. Get into good habits, and make sure they are in order by their number. OptionButton1, OptionButton2, OptionButton3,….etc. This makes it easier for when you code later.
10. Next you need to make the headings for each option, in this case from “Strongly Disagree” to “Strongly Agree”. Use the labels in the toolbox then change the font size. It is easier to do this once, then copy and paste the label four times and change the text within. Again, make sure you change the (Name) in the properties box to reflect each object. Good habit.
11. Using a Label from the toolbox, write your question. See example below.
12. Using the shift key, select the question and the five option buttons. Copy. Paste four times and line them all up. Change the text in the question so you have five different questions. At this point the workbook should be saved. Click on the workbook to select it then click on File/Save as. Name the workbook MyFirstUserForm. Before you click on Save, make sure the format is: Excel Macro-Enabled Workbook (.xlsm).
13. In the toolbox choose TextBox. Place a box at the top of the form. In its property box call it ‘TextBoxParticipantNumber’. Create a label next to it and type in ‘Participant number’.
14. You need to have a Submit button which saves the data closes the form. In the toolbox select CommandButton. Place one on the bottom right of the form. In its properties window call it CommandButtonSubmit. In the button write “SUBMIT” and make the font larger.
15. Finally, you need to group the OptionButtons. This ensures that only one option button may be selected out of a group of five. To do this select all the option buttons in the first row (using your shift key). Then click on Format in the top menu and select Group. In the properties box in group name, name it Question1. This is important. Do this for the rest of the rows of option buttons.
PART TWO – THE CODE BEHIND THE FORM
So you want the responses to be recorded in the worksheet. Easy…
1. Make the worksheet active by clicking on it. In cell A1 write ‘Participant number’.
2. In cell B1 to F1, write Q1, Q2, Q3, Q4, and Q5.
3. Click on the userform to make it the active window. Then click on View on the top Menu, and then Code. The code window for your userform will appear. Cut and paste the code below in the window.
4. Once you look closely at the code you can see why it is important to label your objects. Excel will call upon these objects by their name, they need to be exactly the same. You can also see how each OptionButton links to a specified cell in your worksheet. You should check all your OptionButtons are in numeric order and matches the code. The last question is a reverse type question. You can see that the scores are also reversed in the code.
5. When the form is run, and the participant clicks on submit, the form will save as a file with the participant number in its name. The date and time will also be in the name. It will save to the same location in which the Excel userform is located. To see if it works click on the ‘play’ button in the floating toolbox.
Private Sub TextBoxParticipantNumber_AfterUpdate()
Sheets(“Sheet1”).Range(“A2”).Value = TextBoxParticipantNumber.Text
Private Sub OptionButton1_Click()
Sheets(“Sheet1”).Range(“B2”).Value = “1”
Private Sub OptionButton2_Click()
Sheets(“Sheet1”).Range(“B2”).Value = “2”
Private Sub OptionButton3_Click()
Sheets(“Sheet1”).Range(“B2”).Value = “3”
Private Sub OptionButton4_Click()
Sheets(“Sheet1”).Range(“B2”).Value = “4”
Private Sub OptionButton5_Click()
Sheets(“Sheet1”).Range(“B2”).Value = “5”
Private Sub OptionButton6_Click()
Sheets(“Sheet1”).Range(“C2”).Value = “1”
Private Sub OptionButton7_Click()
Sheets(“Sheet1”).Range(“C2”).Value = “2”
Private Sub OptionButton8_Click()
Sheets(“Sheet1”).Range(“C2”).Value = “3”
Private Sub OptionButton9_Click()
Sheets(“Sheet1”).Range(“C2”).Value = “4”
Private Sub OptionButton10_Click()
Sheets(“Sheet1”).Range(“C2”).Value = “5”
Private Sub OptionButton11_Click()
Sheets(“Sheet1”).Range(“D2”).Value = “1”
Private Sub OptionButton12_Click()
Sheets(“Sheet1”).Range(“D2”).Value = “2”
Private Sub OptionButton13_Click()
Sheets(“Sheet1”).Range(“D2”).Value = “3”
Private Sub OptionButton14_Click()
Sheets(“Sheet1”).Range(“D2”).Value = “4”
Private Sub OptionButton15_Click()
Sheets(“Sheet1”).Range(“D2”).Value = “5”
Private Sub OptionButton16_Click()
Sheets(“Sheet1”).Range(“E2”).Value = “1”
Private Sub OptionButton17_Click()
Sheets(“Sheet1”).Range(“E2”).Value = “2”
Private Sub OptionButton18_Click()
Sheets(“Sheet1”).Range(“E2”).Value = “3”
Private Sub OptionButton19_Click()
Sheets(“Sheet1”).Range(“E2”).Value = “4”
Private Sub OptionButton20_Click()
Sheets(“Sheet1”).Range(“E2”).Value = “5”
Private Sub OptionButton21_Click()
Sheets(“Sheet1”).Range(“F2”).Value = “5”
Private Sub OptionButton22_Click()
Sheets(“Sheet1”).Range(“F2”).Value = “4”
Private Sub OptionButton23_Click()
Sheets(“Sheet1”).Range(“F2”).Value = “3”
Private Sub OptionButton24_Click()
Sheets(“Sheet1”).Range(“F2”).Value = “2”
Private Sub OptionButton25_Click()
Sheets(“Sheet1”).Range(“F2”).Value = “1”
Private Sub CommandButtonSUBMIT_Click()
If Application.CountBlank(Range(“A2:F2”)) > 0 Then
MsgBox “please ensure you have entered your participant number and completed all questions”
Dim wb As Workbook
Dim FolderName As String
FolderName = TextBoxParticipantNumber.Text & _
Format(Now, “_dd-mm-yyyy hh-mm\”)
Application.ScreenUpdating = False
On Error Resume Next
On Error GoTo 0
Set wb = ActiveWorkbook
.SaveAs FolderName & “.xls”, FileFormat:=xlWorkbookNormal
Application.ScreenUpdating = True
PART THREE – FINISHING TOUCHES
1. Now let’s tidy things up. We will place a button that opens clears the data and opens the form. On the worksheet itself, below the second row insert a round rectangle (Insert/shape/roundrectangle). Inside it write “Commence Questionnaire”. I would change the font size to 14 and make it bold.
2. Right click on the rectangle shape and choose ‘Assign Macro…’. Click on New.
3. Delete the code already there and Insert the following:
4. You are finished. Close all windows except the worksheet. Save and close. This way when you open again all you see is the worksheet with the Commence Questionnaire button. Obviously, now you have a form, you can change the questions and scoring as you wish and use this as a template. I hope this has been helpful.