Contextures

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.

survey form with option buttons

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

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

Modifications: You can change the number of Responses (maxBtns) and the number of questions.

survey form with option buttons

The number of response headings must match the maxBtns number, so add or remove headings if necessary.

survey form with option buttons

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
'http://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 file -- run macro SetupSurvey3

survey form with option buttons

Download the Excel Survey Template

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

More Tutorials go to top

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

Search Contextures Sites

 

30 Excel Functions in 30 Days

 

 

 

 

excel tools

 

 

Last updated: November 8, 2016 7:32 PM