How to create a Likert scale in Excel (Mac)

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!

userform
5-item Likert questionnaire example

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.

OpenExcel

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.

ChangeUserForm

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.

PlaceObjects

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.

Submit

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.

pastecode

Option Explicit

Private Sub TextBoxParticipantNumber_AfterUpdate()
Sheets(“Sheet1”).Range(“A2”).Value = TextBoxParticipantNumber.Text
End Sub

Private Sub OptionButton1_Click()
    Sheets(“Sheet1”).Range(“B2”).Value = “1”
End Sub
Private Sub OptionButton2_Click()
    Sheets(“Sheet1”).Range(“B2”).Value = “2”
End Sub
Private Sub OptionButton3_Click()
    Sheets(“Sheet1”).Range(“B2”).Value = “3”
End Sub
Private Sub OptionButton4_Click()
    Sheets(“Sheet1”).Range(“B2”).Value = “4”
End Sub
Private Sub OptionButton5_Click()
    Sheets(“Sheet1”).Range(“B2”).Value = “5”

Private Sub OptionButton6_Click()
    Sheets(“Sheet1”).Range(“C2”).Value = “1”
End Sub
Private Sub OptionButton7_Click()
    Sheets(“Sheet1”).Range(“C2”).Value = “2”
End Sub
Private Sub OptionButton8_Click()
    Sheets(“Sheet1”).Range(“C2”).Value = “3”
End Sub
Private Sub OptionButton9_Click()
    Sheets(“Sheet1”).Range(“C2”).Value = “4”
End Sub
Private Sub OptionButton10_Click()
    Sheets(“Sheet1”).Range(“C2”).Value = “5”

Private Sub OptionButton11_Click()
    Sheets(“Sheet1”).Range(“D2”).Value = “1”
End Sub
Private Sub OptionButton12_Click()
    Sheets(“Sheet1”).Range(“D2”).Value = “2”
End Sub
Private Sub OptionButton13_Click()
    Sheets(“Sheet1”).Range(“D2”).Value = “3”
End Sub
Private Sub OptionButton14_Click()
    Sheets(“Sheet1”).Range(“D2”).Value = “4”
End Sub
Private Sub OptionButton15_Click()
    Sheets(“Sheet1”).Range(“D2”).Value = “5”

 Private Sub OptionButton16_Click()
    Sheets(“Sheet1”).Range(“E2”).Value = “1”
End Sub
Private Sub OptionButton17_Click()
    Sheets(“Sheet1”).Range(“E2”).Value = “2”
End Sub
Private Sub OptionButton18_Click()
    Sheets(“Sheet1”).Range(“E2”).Value = “3”
End Sub
Private Sub OptionButton19_Click()
    Sheets(“Sheet1”).Range(“E2”).Value = “4”
End Sub
Private Sub OptionButton20_Click()
    Sheets(“Sheet1”).Range(“E2”).Value = “5”

Private Sub OptionButton21_Click()
    Sheets(“Sheet1”).Range(“F2”).Value = “5”
End Sub
Private Sub OptionButton22_Click()
    Sheets(“Sheet1”).Range(“F2”).Value = “4”
End Sub
Private Sub OptionButton23_Click()
    Sheets(“Sheet1”).Range(“F2”).Value = “3”
End Sub
Private Sub OptionButton24_Click()
    Sheets(“Sheet1”).Range(“F2”).Value = “2”
End Sub
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”
 Else

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
    ActiveSheet.Copy
  Set wb = ActiveWorkbook
  With wb
      .SaveAs FolderName & “.xls”, FileFormat:=xlWorkbookNormal
  End With
  Application.ScreenUpdating = True
Unload Questionnaire1
ActiveWindow.Close

End If
End Sub

 

 

 

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:

Option Explicit

Sub RoundedRectangle1_Click()
Range(“A2:F2”).ClearContents
Questionnaire1.Show
End Sub

Macro

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.

 

 

 


 Post details 

 Comments (6) 

  1. umar ibrahim says:

    I followed all the steps u mentioned above, but in part two no 3 I don’t understand which window will I past d code. Pls asist me

    • stef says:

      Hi Umar,
      I have added another screen shot for you to show how to get the code window up. Hope it is clearer.
      S

  2. umar ibrahim says:

    Thank 4 d response sir, I mean d window that I will past d codes. After I cut it in d code windoe

  3. umar ibrahim says:

    Hi sir, thanks for making it clearer,my question was that, after I cut d code in which window will I past it.thanks

  4. Irfan Zaidi says:

    Hi Sir, is it possible while answering the questions user could not move anywhere else ..even hi could not use Internet cannot move any other open thing on computer..first solve this questionnaire or abort it. (purpose is, user may not use any helping material available on his computer or internet)

    • stef says:

      Hi Irfan,
      It sounds like you want the person completing the questionnaire not to have any option other than completion or abort the questionnaire. I am not sure this is possible. It may be but I would not know the how this could be done. I would suggest you ask the experts at http://www.mrexcel.com/forum/forum.php. I have found them helpful and could not have completed much of my excel work without the help from this forum.
      Good luck.

 Leave a comment 

Your email address will not be published.


 © 2017 - Automation