Develop Survey or Feedback Form in Excel and VBA

Advertisement

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.

Feedback Form Sample

Please follow the below steps to develop this beautiful and automated form in Excel and VBA.

  1. Open Microsoft Excel application and create a blank workbook.
  2. 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

Saving Excel file with macro enabled extension

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

Insert and rename sheets

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.

Feedback form (please watch YouTube tutorial for more details)

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.

Insert module

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.

assigning the macro

Now, we are done with developing the Feedback form in Excel. You can download the file from the below link.

Advertisement
Meet Dilip Tiwari, a computer graduate with over 15 years of experience in data handling, automation, dashboard design, visualization, and data storytelling. Dilip is certified in MS Excel, Microsoft Certified Power BI Data Analyst, SQL, Google Analytics, UI Designing, Digital Marketing, and Wordpress technology. He also runs a successful YouTube channel, TheDataLabs, with over 100K subscribers. Dilip is passionate about technology and loves sharing his knowledge. Join him on his exciting journey as he explores the world of data and technology.

LEAVE A REPLY

Please enter your comment!
Please enter your name here