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.
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.
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
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:
After running the code, you can customize the survey:
To use the survey:
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.
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
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
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.
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*rc" 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 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:
The code for this survey is in the sample file -- run macro SetupSurvey2
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:
The code for this survey is in the sample file -- run macro SetupSurvey3
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.
Last updated: July 9, 2021 3:50 PM