Home > Templates > Templates > Survey Survey Template With Option ButtonsTo 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 FormUse 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. |
Manual SetupTo 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 |
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 QuestionsIn 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 CodeRun 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 3Survey 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 |
Get the Excel Survey TemplateClick 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 TutorialsSelect Answers With Option Buttons Calculate Survey Scores with Option Buttons Show Survey Responses in Pivot Chart |
Last updated: December 31, 2022 3:34 PM