Survey Template With Option Buttons

To create an Excel survey template, you can use the Options Button controls from the Forms toolbar. Do this manually, or use the sample code from this tutorial, to create it programmatically.

Thanks to Dave Peterson, who wrote this technique.

Create a Survey Form

Use option button groups on a worksheet, to collect survey responses. Weight the questions, and calculate the total score.

The sample file for this tutorial has three survey examples, and the instructions are shown below. The VBA code for Survey Form 1 is also shown below, and code for the other surveys is in the sample file.

Manual Setup

To create a survey form on a worksheet, you could create a template manually, adding and aligning the option buttons. Watch this video to see how to set up groups of options buttons, and click this link for instructions on setting up groups of option buttons: Select Answers With Option Buttons

Survey Form 1

To save time when creating a survey form, you can use programming to add the text, option buttons, and formulas. This sample survey calculates a total score, based on the response numbers (1 to 5), and the scores can be weighted.

To set up Survey 1, follow these steps:

1. Copy the Survey 1 code onto a regular worksheet module in your workbook.
2. Select the blank sheet where you want to create the survey -- the macro will create the survey on the active sheet
3. To run the code, click the View tab on the Ribbon, then click Macros
4. Select the SetupSurvey macro in the list, and click Run.
5. The macro will create 10 numbered questions with 5 option buttons for each question. (Those settings can be changed in the code.)

After running the code, you can customize the survey:

1. Add formatting and heading text to the survey template (optional)
2. Weighting in column B can be changed from 1 to any value.

To use the survey:

1. Click on an option button for each question, to select a response
2. The raw score for each question is shown in column C
3. The weighted score for each question is shown in column A (raw score x weight)
4. The survey weighted total is shown in cell A1. (=SUM(A2:A11))
5. To clear a selected option, delete the score in column C for that row.

Code to Create Survey Form 1

Copy the following code into a regular code module in a workbook. It can be stored in the same file as the survey, or a different workbook. Then, select the sheet where you want the survey, and run the code once, to create the survey template.

Optional - Modifications to the Code

Group Box Borders

Group boxes have a thin black border, and there's no way to change the line colour or thickness. However, you can change the group box Visible setting to False, so the borders won't show on the worksheet.

In the code, change this line (where True is the setting, and False is commented out):

 .Visible = True 'False

to this, where False is the setting:

 .Visible = False

Hide Existing Group Boxes

If you've already created the survey form, with the Group Boxes visible, you can add the following code to your workbook.

Then, with the Survey sheet active, run the code to hide the Group Boxes.

Sub HideGroupBoxes()
Dim myGB As GroupBox
Dim ws As Worksheet
Set ws = ActiveSheet

For Each myGB In ws.GroupBoxes
  myGB.Visible = False
Next myGB

End Sub

Number of Responses and Questions

In the code, you can change the number of Responses (maxBtns) and the number of questions.

NOTE: If you change the maxBtns number, add or remove response headings, in the following line of code. The count of headings here, must match the maxBtns number.

Survey Form 1 Code

Run this code once, to set up the survey with questions and option buttons.

Sub SetupSurveyForm()
'code written by Dave Peterson
'creates a survey form with option buttons
'https://www.contextures.com/xlForm01.html
Dim grpBox As GroupBox
Dim optBtn As OptionButton
Dim maxBtns As Long
Dim myCell As Range
Dim myRange As Range
Dim wks As Worksheet
Dim iCtr As Long
Dim FirstOptBtnCell As Range
Dim NumberOfQuestions As Long
Dim myBorders As Variant

myBorders = Array(xlEdgeLeft, xlEdgeTop, _
  xlEdgeBottom, xlEdgeRight, _
  xlInsideVertical, xlInsideHorizontal)

maxBtns = 5
NumberOfQuestions = 10

Set wks = ActiveSheet
With wks
  Set FirstOptBtnCell = .Range("E2")
  .Range("A:I").Clear
  With FirstOptBtnCell.Offset(-1, -1) _
        .Resize(1, maxBtns + 1)
    .Value = Array("Question#", _
          "Resp1", "Resp2", _
          "Resp3", "Resp4", "Resp5")
    .Orientation = 90
    .HorizontalAlignment = xlCenter
  End With

  Set myRange = FirstOptBtnCell _
    .Resize(NumberOfQuestions, 1)

  With myRange.Offset(0, -1)
      .Formula = "=row()-" _
        & myRange.Row - 1
      .Value = .Value
  End With

  myRange.Offset(0, -3).Value = 1

  With myRange.Offset(0, -4)
    .FormulaR1C1 = "=rc[1]*rc[2]"
  End With

  .Range("A1").Formula = "=SUM(A2:A" _
    & NumberOfQuestions + 1 & ")"

  With myRange.Offset(0, -4) _
      .Resize(, 4)
    For iCtr = LBound(myBorders) _
      To UBound(myBorders)
      With .Borders(myBorders(iCtr))
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
      End With
    Next iCtr
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
  End With

  myRange.EntireRow.RowHeight = 28
  myRange.Resize(, maxBtns) _
    .EntireColumn.ColumnWidth = 4

  'clean up existing junk
  .GroupBoxes.Delete
  .OptionButtons.Delete

End With

For Each myCell In myRange
  With myCell.Resize(1, maxBtns)
    Set grpBox = wks.GroupBoxes.Add _
        (Top:=.Top, Left:=.Left, _
          Height:=.Height, _
          Width:=.Width)
    With grpBox
      .Caption = ""
      .Visible = True 'False
    End With
  End With
  For iCtr = 0 To maxBtns - 1
    With myCell.Offset(0, iCtr)
      Set optBtn = wks.OptionButtons.Add _
        (Top:=.Top, Left:=.Left, _
        Height:=.Height, Width:=.Width)
      optBtn.Caption = ""
      If iCtr = 0 Then
        With myCell.Offset(0, -2)
          optBtn.LinkedCell _
            = .Address(external:=True)
        End With
      End If
    End With
  Next iCtr
Next myCell
End Sub

Survey Form 2

Survey Form 2 is similar to Survey Form 1, but the setup macro prompts you for the number of questions -- you don't have to modify that setting in the code.

This survey subtracts 1 from the option button values, to create scores from 0 to 4. It also has an "N/A" response.

The form calculates a total score, and the scores can be weighted. Here is the formula in column A:

=IF(C2="","",IF(C2=6,"N/A",B2*(C2-1)))

The code for this survey is in the sample file -- run macro SetupSurvey2

Survey Form 3

Survey Form 3 is similar to Survey Form 2 -- the setup macro prompts you for the number of questions, and it 5 responses with values, and an "N/A" response. You can assign a score to each response value, in a lookup table.

The form uses an INDEX and MATCH formula to calculate the scores assigned to the response numbers, and the scores can be weighted. Here is the formula in column A:

=IF(C2="","",IF(C2=6,"N/A",B2*INDEX(ScoreList,MATCH(C2,RespList,0))))

The code for this survey is in the sample file -- run macro SetupSurvey3

Get the Excel Survey Template

Click here to get the zipped sample survey template. The zipped file is in xlsm format, and contains macros. All three variations of the Survey Form are in the sample file, on separate sheets.

More Tutorials

Select Answers With Option Buttons

Calculate Survey Scores with Option Buttons

Show Survey Responses in Pivot Chart

Data Entry Worksheet Form

Print Selected Items in Order Form

Data Entry and Update Form