How to develop customer survey/feedback form in Microsoft Excel and VBA?
Survey or Feedback form helps business to know what their customer think about their business and services. With the help of feedback data, business can make the required changes in services or employee behavior so that customer can be satisfied.
You can find several options to collect the customer feedback and from all those options, Excel is one which is easy to develop Survey or Feedback from.
In this article, we will develop a customer survey or feedback form in Excel. This step-by-step tutorial will help you in developing the form even if you are new to Excel and VBA.
Here, we are going to take the feedback questions of hotel industry but with the same logic and VBA codes, you can develop survey or feedback form for any of the businesses.
Please follow the below steps to develop this beautiful and automated form in Excel and VBA.
- Open Microsoft Excel application and create a blank workbook.
- Save Excel Workbook with the name “Hotel Feedback Form in Excel and VBA.xlsm”. Don’t forget to select macro enabled extension i.e. .xlsm
3. Insert two worksheets in current workbook and rename to ‘Form’ and ‘Database’.
4. In ‘Database’ worksheet, create column headers with below mentioned column names from Column A to U (in same sequence). This will be utilized as a ‘Database’ to transfer all the feedback from 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 house keeping 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 hotel’s restaurant?
How would you rate our Travel Desk’s staff?
How did you find the overall cleanliness of 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. Now, create the form in sheet ‘Form’ as below. Here, we have to use Textbox, Option Buttons and Command Button to create this form.
6. Move to Visual Basic Application window and start writing the code. To move to VBA window, press the shortcut key ALT + F11. Once you press the shortcut then it will open a separate VBA application on top of Excel window.
7. In VBA window, insert a blank module. To insert the module, click on Insert menu and then click on Module in drop-down menu.
8. Click on module in project explorer window and start writing the code in code window.
9. Write the below code to reset the 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 module to validate the inputs provided by customer in Feedback 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 database sheet.
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. Now assign the macro to submit and reset button available in sheet ‘form’. To assign the macro, right click on the button and then click on assign macro. In assign macro window, select the Submit from available macro and then click on OK. Repeat the same steps to assign macro on Reset button.
Now, we are done with developing the Feedback form in Excel. You can download the file from the below link.
