Contextures

Home > Templates > Templates > Survey

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.

survey form with option buttons

Thanks to Dave Peterson, who wrote this technique.

Create a Survey Form

Manual Setup

Survey Form 1

Survey Form 2

Survey Form 3

Get the Sample File

Create a Survey Form

Use option button groups on a worksheet, to collect survey responses. Weight the survey questions, if needed, 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.

survey form with option buttons

Manual Setup

To create a survey form on a Microsoft Excel 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 on an Excel spreadsheet. For the written steps, 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 new survey form, for multiple choice response type questions, you can use programming to add the text, option buttons, and formulas.

This kind of survey calculates a total score, based on the response numbers (1 to 5), and the scores can be weighted in the calculations.

For example, you could set up a customer service satisfaction survey, and calculate the total for each submission

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.)

survey form with option buttons

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.

survey form with option buttons

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 an Excel 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.

survey form with option buttons

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 with option buttons

Survey Form 1 Code

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

Note: The first cell with an option button is set as E5 in the code. 

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.

survey form with option buttons

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

survey form with option buttons

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 Excel file -- run macro SetupSurvey3

survey form with option buttons

Get the Excel Survey Template

Click here to download 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

 

 

More Tutorials

Select Answers With Excel Option Buttons

About Debra

 

Last updated: December 31, 2022 3:34 PM