Develop a Dynamic Survey or Feedback Form in Excel and VBA – 3 easy steps

How to Develop a Develop a Dynamic Survey or Feedback Form in Excel and VBA?

Survey or feedback forms help businesses to understand what their customers think about their products and services. With feedback data, businesses can make the necessary changes to improve their services or employee behavior to satisfy their customers. Microsoft Excel provides an easy option to develop survey or feedback forms.

YouTube Tutorials to develop a Dynamic Survey or Feedback Form in Excel and VBA

In this step-by-step tutorial, we will develop a dynamic survey or feedback form in Excel and VBA. We will use the feedback questions of the hotel industry, but with the same logic and VBA codes, you can develop survey or feedback forms for any business.

Survey or Feedback Form in Excel and VBA – @TheDataLabs

Step-by-step Tutorial to Develop a Dynamic Survey or Feedback Form in Excel and VBA

Let’s get started by following the steps below to develop a dynamic survey or feedback from in Excel and VBA:

Develop a Dynamic Survey or Feedback Form in Excel and VBA
How to Develop a Survey or Feedback Form in Excel and VBA
  1. Open Microsoft Excel application and create a blank workbook.
  2. Save the Excel workbook with the name “Hotel Feedback Form in Excel and VBA.xlsm”. Remember to select the macro-enabled extension i.e. .xlsm.
image 1
Saving Excel file with macro enabled extension

3. Insert two worksheets in the current workbook and rename them to ‘Form’ and ‘Database’.

image 2
Insert and rename sheets

4. In the ‘Database’ worksheet, create column headers with the following column names from Column A to U (in the same sequence). This will be used as a ‘Database’ to transfer all the feedback from the customer or user.

  • S. No.
  • Name
  • Gender
  • Mobile Number
  • Email ID
  • Address
  • How did our front office staff behave during your stay?
  • How did you find the overall cleanliness of our hotel?
  • How did you find the cleanliness of your room?
  • How was housekeeping behaving during your stay?
  • How was the ambiance of the hotel’s restaurant?
  • How was the restaurant’s food from our hotel?
  • How did you find the overall cleanliness of the hotel’s restaurant?
  • How would you rate our Travel Desk’s staff?
  • How did you find the overall cleanliness of the swimming pool?
  • How would you rate our Spa services?
  • How would you rate our hotel, overall?
  • Would you recommend our hotel to other people?
  • Submitted On
  • Submitted By

5. Create the form in sheet ‘Form’ as below. We will use Textbox, Option Buttons, and Command Button to create this form.

image
Feedback form (please watch YouTube tutorial for more details)

6. Move to the Visual Basic Application window and start writing the code. To move to the VBA window, press the shortcut key ALT + F11. Once you press the shortcut, it will open a separate VBA application on top of the Excel window.

7. In the VBA window, insert a blank module. To insert the module, click on the Insert menu and then click on Module in the drop-down menu.

Module
Insert module

8. Click on the module in the project explorer window and start writing the code in the code window.

9. Write the below code to reset the form.

VBA Code to Reset the Survey Form

   Sub Reset_Form()

    Sheet1.txtName.Value = ""
    
    Sheet1.optFemale.Value = False
    Sheet1.optMale.Value = False
    Sheet1.txtMobile.Value = ""
    Sheet1.txtEmail.Value = ""
    Sheet1.txtAddress.Value = ""
    
    'Question - 1
    Sheet1.Q1_Excellent.Value = False
    Sheet1.Q1_Good.Value = False
    Sheet1.Q1_Fair.Value = False
    Sheet1.Q1_Poor.Value = False

    'Question - 2
    Sheet1.Q2_Excellent.Value = False
    Sheet1.Q2_Good.Value = False
    Sheet1.Q2_Fair.Value = False
    Sheet1.Q2_Poor.Value = False
    
    'Question - 3
    Sheet1.Q3_Excellent.Value = False
    Sheet1.Q3_Good.Value = False
    Sheet1.Q3_Fair.Value = False
    Sheet1.Q3_Poor.Value = False
    
    'Question - 4
    Sheet1.Q4_Excellent.Value = False
    Sheet1.Q4_Good.Value = False
    Sheet1.Q4_Fair.Value = False
    Sheet1.Q4_Poor.Value = False
    
    'Question - 5
    Sheet1.Q5_Excellent.Value = False
    Sheet1.Q5_Good.Value = False
    Sheet1.Q5_Fair.Value = False
    Sheet1.Q5_Poor.Value = False
    
    'Question - 6
    Sheet1.Q6_Excellent.Value = False
    Sheet1.Q6_Good.Value = False
    Sheet1.Q6_Fair.Value = False
    Sheet1.Q6_Poor.Value = False
    
    'Question - 7
    Sheet1.Q7_Excellent.Value = False
    Sheet1.Q7_Good.Value = False
    Sheet1.Q7_Fair.Value = False
    Sheet1.Q7_Poor.Value = False
    
    'Question - 8
    Sheet1.Q8_Excellent.Value = False
    Sheet1.Q8_Good.Value = False
    Sheet1.Q8_Fair.Value = False
    Sheet1.Q8_Poor.Value = False
    
    'Question - 9
    Sheet1.Q9_Excellent.Value = False
    Sheet1.Q9_Good.Value = False
    Sheet1.Q9_Fair.Value = False
    Sheet1.Q9_Poor.Value = False
    
    'Question - 10
    Sheet1.Q10_Excellent.Value = False
    Sheet1.Q10_Good.Value = False
    Sheet1.Q10_Fair.Value = False
    Sheet1.Q10_Poor.Value = False
    
    'Question - 11
    Sheet1.Q11_Excellent.Value = False
    Sheet1.Q11_Good.Value = False
    Sheet1.Q11_Fair.Value = False
    Sheet1.Q11_Poor.Value = False
    
    'Question - 12
    Sheet1.Q12_Yes.Value = False
    Sheet1.Q12_No.Value = False
    
End Sub


11. Write the below function in the module to validate the inputs provided by the customer in the Feedback form.

VBA Code to Validate the Survey Form

Function Validate() As Boolean

    Validate = True
    
    If Sheet1.txtName.Value = "" Then
        MsgBox "Name is blank."
        Validate = False
        Exit Function
    End If

    If Sheet1.optFemale.Value = False And Sheet1.optMale.Value = False Then
        MsgBox "Please select the gender."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.txtMobile.Value = "" Then
        MsgBox "Mobile Number is blank."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.txtEmail.Value = "" Then
        MsgBox "Email ID is blank."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.txtAddress.Value = "" Then
        MsgBox "Address is blank."
        Validate = False
        Exit Function
    End If
    
    
    If Sheet1.Q1_Excellent.Value = False And Sheet1.Q1_Good.Value = False And Sheet1.Q1_Fair.Value = False And Sheet1.Q1_Poor.Value = False Then
        MsgBox "Please select the option for Question 1."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.Q2_Excellent.Value = False And Sheet1.Q2_Good.Value = False And Sheet1.Q2_Fair.Value = False And Sheet1.Q2_Poor.Value = False Then
        MsgBox "Please select the option for Question 2."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.Q3_Excellent.Value = False And Sheet1.Q3_Good.Value = False And Sheet1.Q3_Fair.Value = False And Sheet1.Q3_Poor.Value = False Then
        MsgBox "Please select the option for Question 3."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.Q4_Excellent.Value = False And Sheet1.Q4_Good.Value = False And Sheet1.Q4_Fair.Value = False And Sheet1.Q4_Poor.Value = False Then
        MsgBox "Please select the option for Question 4."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.Q5_Excellent.Value = False And Sheet1.Q5_Good.Value = False And Sheet1.Q5_Fair.Value = False And Sheet1.Q5_Poor.Value = False Then
        MsgBox "Please select the option for Question 5."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.Q6_Excellent.Value = False And Sheet1.Q6_Good.Value = False And Sheet1.Q6_Fair.Value = False And Sheet1.Q6_Poor.Value = False Then
        MsgBox "Please select the option for Question 6."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.Q7_Excellent.Value = False And Sheet1.Q7_Good.Value = False And Sheet1.Q7_Fair.Value = False And Sheet1.Q7_Poor.Value = False Then
        MsgBox "Please select the option for Question 7."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.Q8_Excellent.Value = False And Sheet1.Q8_Good.Value = False And Sheet1.Q8_Fair.Value = False And Sheet1.Q8_Poor.Value = False Then
        MsgBox "Please select the option for Question 8."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.Q9_Excellent.Value = False And Sheet1.Q9_Good.Value = False And Sheet1.Q9_Fair.Value = False And Sheet1.Q9_Poor.Value = False Then
        MsgBox "Please select the option for Question 9."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.Q10_Excellent.Value = False And Sheet1.Q10_Good.Value = False And Sheet1.Q10_Fair.Value = False And Sheet1.Q10_Poor.Value = False Then
        MsgBox "Please select the option for Question 10."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.Q11_Excellent.Value = False And Sheet1.Q11_Good.Value = False And Sheet1.Q11_Fair.Value = False And Sheet1.Q11_Poor.Value = False Then
        MsgBox "Please select the option for Question 11."
        Validate = False
        Exit Function
    End If
    
    If Sheet1.Q12_Yes.Value = False And Sheet1.Q12_No.Value = False Then
        MsgBox "Please select the option for Question 12."
        Validate = False
        Exit Function
    End If

End Function


12. Write the below sub-procedure to submit the feedback form data to the database sheet.

VBA Code to Submit the Survey Form Data

Sub Submit()
    
    Dim confirmation As VbMsgBoxResult
    
    Dim sh As Worksheet
    
    Dim iRow As Integer
    
    confirmation = MsgBox("Do you want to submit the Feedback.", vbYesNo + vbQuestion, "Submit")
    
    If confirmation = vbNo Then Exit Sub
    
    
    If Validate = False Then Exit Sub
    
    Set sh = ThisWorkbook.Sheets("Database")
    
    iRow = sh.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    With sh
    
        sh.Cells(iRow, 1).Value = iRow - 1
        sh.Cells(iRow, 2).Value = Sheet1.txtName.Value
        sh.Cells(iRow, 3).Value = IIf(Sheet1.optFemale.Value = True, "Female", "Male")
        sh.Cells(iRow, 4).Value = Sheet1.txtMobile.Value
        sh.Cells(iRow, 5).Value = Sheet1.txtEmail.Value
        sh.Cells(iRow, 6).Value = Sheet1.txtAddress.Value
        sh.Cells(iRow, 7).Value = IIf(Sheet1.Q1_Excellent.Value = True, "Excellent", IIf(Sheet1.Q1_Good.Value = True, "Good", IIf(Sheet1.Q1_Fair.Value = True, "Fair", "Poor")))
        sh.Cells(iRow, 8).Value = IIf(Sheet1.Q2_Excellent.Value = True, "Excellent", IIf(Sheet1.Q2_Good.Value = True, "Good", IIf(Sheet1.Q2_Fair.Value = True, "Fair", "Poor")))
        sh.Cells(iRow, 9).Value = IIf(Sheet1.Q3_Excellent.Value = True, "Excellent", IIf(Sheet1.Q3_Good.Value = True, "Good", IIf(Sheet1.Q3_Fair.Value = True, "Fair", "Poor")))
        sh.Cells(iRow, 10).Value = IIf(Sheet1.Q4_Excellent.Value = True, "Excellent", IIf(Sheet1.Q4_Good.Value = True, "Good", IIf(Sheet1.Q4_Fair.Value = True, "Fair", "Poor")))
        sh.Cells(iRow, 11).Value = IIf(Sheet1.Q5_Excellent.Value = True, "Excellent", IIf(Sheet1.Q5_Good.Value = True, "Good", IIf(Sheet1.Q5_Fair.Value = True, "Fair", "Poor")))
        sh.Cells(iRow, 12).Value = IIf(Sheet1.Q6_Excellent.Value = True, "Excellent", IIf(Sheet1.Q6_Good.Value = True, "Good", IIf(Sheet1.Q6_Fair.Value = True, "Fair", "Poor")))
        sh.Cells(iRow, 13).Value = IIf(Sheet1.Q7_Excellent.Value = True, "Excellent", IIf(Sheet1.Q7_Good.Value = True, "Good", IIf(Sheet1.Q7_Fair.Value = True, "Fair", "Poor")))
        sh.Cells(iRow, 14).Value = IIf(Sheet1.Q8_Excellent.Value = True, "Excellent", IIf(Sheet1.Q8_Good.Value = True, "Good", IIf(Sheet1.Q8_Fair.Value = True, "Fair", "Poor")))
        sh.Cells(iRow, 15).Value = IIf(Sheet1.Q9_Excellent.Value = True, "Excellent", IIf(Sheet1.Q9_Good.Value = True, "Good", IIf(Sheet1.Q9_Fair.Value = True, "Fair", "Poor")))
        sh.Cells(iRow, 16).Value = IIf(Sheet1.Q10_Excellent.Value = True, "Excellent", IIf(Sheet1.Q10_Good.Value = True, "Good", IIf(Sheet1.Q10_Fair.Value = True, "Fair", "Poor")))
        sh.Cells(iRow, 17).Value = IIf(Sheet1.Q11_Excellent.Value = True, "Excellent", IIf(Sheet1.Q11_Good.Value = True, "Good", IIf(Sheet1.Q11_Fair.Value = True, "Fair", "Poor")))
        sh.Cells(iRow, 18).Value = IIf(Sheet1.Q12_Yes.Value = True, "Yes", "No")
        sh.Cells(iRow, 19).Value = Application.UserName
        sh.Cells(iRow, 20).Value = [Today()]
        
    End With
    
    MsgBox "Feedback Submitted. Thanks!"

End Sub

13. Assign the macro to submit and reset buttons available in sheet ‘form’. To assign the macro, right-click on the button and then click on assign macro. In the assign macro window, select the Submit from available macro, and then click on OK. Repeat the same steps to assign a macro on the Reset button.

image 1
assigning the macro

Download the demo file used for the tutorial

Now, we have successfully developed the Feedback form in Excel. You can download the file from the below link.

Download

If you have any questions about this tutorial ‘Develop a Dynamic Survey or Feedback Form in Excel and VBA’, please leave a comment and TheDataLabs team will respond to you as soon as possible.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Captcha verification failed!
CAPTCHA user score failed. Please contact us!

Recommended Reads

Latest Articles