Excel Survey Generator

Create instantly surveys adapted to your needs with Excel Survey Generator. Select from a wide range of response options and get a printable or presentation survey layout, also with the possibility to answer directly in the Excel spreadsheet. 

Survey Information

The Excel Survey Generator form shown below serves to input the necessary information to create the survey. That includes the following fields:

• Survey title and description
• Total number of questions
• Range and type of responses
• Confirmation to allow additional comments
• Layout orientation and design

The survey title field can be empty and filled later directly into the survey sheet, and the description is optional. The total number of questions is mandatory and up to 20 questions (but easily scalable if needed). A survey with 20 questions and additional comments box fits into a portrait A4 page, and 10 questions into a landscape A4 page. The questions can be manually entered into the “Questions” worksheet, or later directly in the survey sheet.

Excel Survey Generator comes with predetermined responses based on the number of response options (between 2 and 5) and the following five different response categories:

• Certainty
• Quality
• Periodicity
• Agreement
• Satisfaction

The predetermined responses can be manually updated in the worksheet “Responses”. Note the responses are separated by a forward slash (/) in order to allow the macro to read it correctly and segregate each individual response. That’s achieved in the VBA code by transforming the string into an array of as many individual responses separated by the forward slash as there are with the Split function.

  ResponseOptions = Split(ResponseOptions, "/")

  ResponseOptions(0)

  ResponseOptions(1)

  …

A box for additional comments can be added at the bottom of the survey layout in Excel Survey Generator. The layout orientation can set to either portrait or landscape, and the design to black&white (ideal for printing purposes) or color (more suitable for presentation).

Survey Layout

As explained earlier, Excel Survey Generator allows different layout options. The generated survey shows as a grid of questions and response options, depending on pre-selected parameters. The macro uses different VBA methods to format the worksheet each time, with exact numbers to let the survey fit into an A4 portrait or landscape layout. Here’s how the Border and BorderAround methods are used to format a grid or other borders to cells and ranges in the survey layout.

  With MySheet.MyRange

      'adds border to all cells within range

      .Borders.LineStyle = xlContinuous

      'adds border at the bottom of the range

      .Borders(xlEdgeBottom).LineStyle = xlContinuous

   

      'adds border at the bottom with particular color (using vbColor)

      .Borders(xlEdgeBottom).Color = vbBlack

      'adds border around the range with particular thickness

      'and color (using ColorIndex)

      .BorderAround ColorIndex:=2, Weight:=xlThick

  End With

The survey layout allows to fill in the answers directly into the survey worksheet of Excel Survey Generator. In order to ensure unique response entry, an event procedure is triggered upon changed selection in that worksheet. The procedure checks for the cell selected and if it is within the response grid range. To do so, it gets the coordinates of that range by getting the total number of questions and responses with the following two worksheet functions:

  TotalQuestions = WorksheetFunction.Max(Range("B:B"))

  TotalResponses = WorksheetFunction.CountA(Range("H5:L5"))

The check mark in the responses grid appears by changing the font name of those cells with .Font.Name = "Wingdings" and value to "ü".

Click Here to download the Excel Survey Generator.