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.
Modifications: You can change the number of Responses (maxBtns) and the number of questions.
The number of response headings must match the maxBtns number, so add or remove headings if necessary.
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*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 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.
Last updated: November 15, 2017 2:36 PM