Table of Contents
How to 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.
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:
- Open Microsoft Excel application and create a blank workbook.
- 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.
3. Insert two worksheets in the current workbook and rename them to ‘Form’ and ‘Database’.
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.
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.
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.
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.
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.
