Call Center Audit Form in VBA & Excel

What is Call Center Transaction Monitoring?

Call Center Transaction Monitoring, also known as call audit, is an ongoing process of closely monitoring of some of the random inbound/outbound calls and sharing feedback with agents basis on audit result. It helps agents in following the best practices and identifying the areas of opportunities. Call audits are done by Quality Analyst (QA) or Team Leader (TL) using call center audit forms. It helps company in increasing productivity and quality by identifying the area of improvement and providing required coaching to the employees.

Call Center Audit Form
Call Center Audit Form
Secure Payments
Why Excel based monitoring form?

Microsoft Excel is a platform which allows us to develop a fully automated and customized template, tools and reports for our business requirement. We don’t need to get an additional license for that. Also, we don’t need to invest in acquiring some additional tools for monitoring. In call center, almost all employees have MS Excel installed at their machine and this is the added advantage. We can simply download the Monitoring Template and start using after making some adjustment, if required.

About Call Center Quality Audit Form provided by TheDataLabs

TheDataLabs team has developed this premium Call Center Transaction Monitoring form in Excel & VBA. It has all the features e.g. Monitoring Form, Dashboard, Auto Email for Feedback and Audit Database.

Our team has tried to developed this form in such a way that it can be easy for any of the companies to start using the template in day to day call monitoring, creating repository of all the audits and sending feedback to agents with auto-feedback facility.

Understanding the Audit Form, Structure and VBA Code
Audit Form

Audit form has five different sections: 1. Basic Information, 2. Buttons and Controls, 3. Audit Score Summary, 4. Audit Details (Parameters, Sub-Parameters and Audit Results) and 5. Overall Feedback

The first section, ‘Employee & Other Details‘, is for basic information updated by QA/TL while auditing the calls. It has all the basic fields which are required for a call audit and providing feedback. In this section, we have fields like ‘Employee ID’, ‘Employee Name’, ‘Employee Email ID’, ‘Client Name’, ‘Call Date’, ‘Transaction ID’, ‘Auditor’s Name’, ‘Audit Date’ and ‘Feedback Shared’. All of the fields are free text input except ‘Feedback Shared’. ‘Feedback Shared’ field is a drop-down field with value ‘Yes’ and ‘No’. Auditor’s Name and Audit Date are auto populated fields but both can be changed by auditor, if required.

Audit Form Basic Information
Basic information

The second section has all the controls to submit the call monitoring details to Audit Dump sheet, creating an email for feedback and resetting the entire form.

Controls and Buttons
VBA Controls and Buttons

The third section provides the category-wise and overall scores. It generates real-time summary and calculates score basis the audit inputs provided by auditors.

Audit Score Summary
Call Audit Score summary

The fourth section is ‘Audit Details’. It has all the parameters and sub-parameters on which auditor will perform the call audits. This template has Categories (Greeting, Courtesy & Communication, Resolution, Documentation and Process Requirement) and Sub-Categories (as mentioned in below image) along with Weightage, Audit Result (input fields) and Comments (input fields).

Audit Details
Audit details

The fifth section is for overall feedback. Auditor can update the overall feedback, opportunity areas and other remarks. It will be saved in ‘Audit Dump’ sheet so that anyone can refer the data in future or to prepare report, if required.

Feedback
Feedback section
Structure of Audit Form

In this audit form, we have three different Worksheets. 1. Form, 2. Audit Dump and 3. Email Summary.

Form sheet has all the input fields, buttons and others to perform the audits and share the feedback.

Audit Dump sheet is to maintain the repository of all the audits done by auditor. Once, user will click on ‘Save’ button to transfer the audit data and feedback, VBA code will update the details below the respective columns in ‘Audit Dump’ sheet.

The third sheet, Email Summary has been used for internal purpose only. VBA utilized this sheet to auto-populate all the required data and then draft an email and embed this sheet data as picture to send the audit feedback to respective agent.

VBA Code used in the Call Center Quality Audit Form

In this project, we have used three different modules and some event based VBA procedure to perform all the required actions.

Modules and Codes
VBA Projects

Feedback Email Code – Below code has been used to draft an email to send the feedback to agent along with all the required summary in mail body.

Sub Feedback_Email()
   
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    On Error GoTo err_msg
        
    Dim OutApp As Object
    Dim OutMail As Object
    Dim shForm As Worksheet
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set shForm = ThisWorkbook.Sheets("Form")
    

    With OutMail
    
        .To = shForm.Range("D11").Value
        .cc = "info@thedatalabs.org"
        .Subject = "Call Audit Feedback - " & shForm.Range("D11").Value
        .HTMLBody = "<P> Dear " & shForm.Range("D10").Value & ",<br><br>Below is the audit summary. <br>" & RangetoHTML(ThisWorkbook.Sheets("Email Summary").Range("A1:M49")) & "<br>Regards,<br>" & shForm.Range("L9").Value & "</P>"
        .display
        '.Send ' remove comment if you want to send the email without reviewing it
        
    End With

Exit Sub

err_msg:
    
    Load frm_Error
    frm_Error.Label1.ControlTipText = "Error Number:-" & Err.Number & vbLf & vbLf & " || Error Detail:- " & Err.Description & " || "
    frm_Error.Show False
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
        
End Sub

VBA code to convert Audit details to HTML

'VBA Function to convert given range to HTML

Function RangetoHTML(rng As Range)

    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "\" & VBA.Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = VBA.Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")


    TempWB.Close savechanges:=False

    VBA.Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    
End Function

VBA Code to Initialize or Reset the Form

' Procedure to initialize the Cells, Controls

Sub InitializeSheet()

    Application.ScreenUpdating = False

    Dim shForm As Object

    Set shForm = ThisWorkbook.Sheets("Form")

    shForm.Activate

    shForm.Unprotect Password:="thedatalabs"
    
    
    'Employee & Other Details

    shForm.Range("D9:D11").Value = ""

    shForm.Range("H9:H11").Value = ""

    shForm.Range("L9:L11").Value = ""
    
    'Audit Score & Comments
    
    shForm.Range("J32:J34").Value = ""
    shForm.Range("L32:L34").Value = ""
    
    shForm.Range("J38:J42").Value = ""
    shForm.Range("L38:L42").Value = ""
    
    shForm.Range("J46:J50").Value = ""
    shForm.Range("L46:L50").Value = ""
        
    shForm.Range("J54:J55").Value = ""
    shForm.Range("L54:L55").Value = ""
    
    shForm.Range("J59:J60").Value = ""
    shForm.Range("L59:L60").Value = ""
    
    ' Feedbak/Remarks
    
    shForm.Range("B64").Value = ""

   'Adding Validation to feedback Shared Yes/No
    
    shForm.Range("L11").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No"
    End With


    'Adding Validation
    'Greeting & Closing Script

    shForm.Range("J32").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("J33").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("J34").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    'Courtesy & Communication

    shForm.Range("J38").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("J39").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("J40").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("J41").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("J42").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With


    'Issue Resolution

    shForm.Range("J46").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("J47").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("J48").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With


    shForm.Range("J49").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("J50").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    'Case Documentation

    shForm.Range("J54").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("J55").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    'Process Compliance

    shForm.Range("J59").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("J60").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No,N/A"
    End With

    shForm.Range("L9").Value = Application.UserName
    shForm.Range("L10").Value = [Today()]
    
    shForm.optYes = False
    shForm.optNo = False

    shForm.Activate
    shForm.Range("D9").Select

    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollColumn = 1

   shForm.Protect Password:="thedatalabs"

    Application.ScreenUpdating = True

End Sub

VBA Function to take the confirmation before resetting the form

'Procedure to Reset the form

Sub Reset()

    Dim iMsg As Integer

    iMsg = MsgBox("Do you want to reset this form?", vbYesNo + vbQuestion, "Reset Confirmation")

    If iMsg = vbYes Then

        Call InitializeSheet

    End If


End Sub

VBA function to Validate the Audit Form entries

'Function to Validate the Entry

Function CheckEntry() As Boolean

    Dim shForm As Object

    Set shForm = ThisWorkbook.Sheets("Form")

    CheckEntry = True

    '1st Section

    If Trim(shForm.Range("D9").Value) = "" Then

        MsgBox "Employee ID can't be blank.", vbOKOnly + vbInformation, "Employee ID"
        Range("D9").Select
        CheckEntry = False
        Exit Function

    End If

    If Trim(shForm.Range("D10").Value) = "" Then

        MsgBox "Employee Name can't be blank.", vbOKOnly + vbInformation, "Employee Name"
        Range("D10").Select
        CheckEntry = False
        Exit Function

    End If

    If Trim(shForm.Range("D11").Value) = "" Then

        MsgBox "Employee Email ID can't be blank.", vbOKOnly + vbInformation, "Email ID"
        Range("D11").Select
        CheckEntry = False
        Exit Function

    End If

    

    If Trim(shForm.Range("H9").Value) = "" Then

        MsgBox "Client Name can't be blank.", vbOKOnly + vbInformation, "Client Name"
        Range("H9").Select
        CheckEntry = False
        Exit Function

    End If
    
    
    If Trim(shForm.Range("H10").Value) = "" Then

        MsgBox "Call Date can't be blank.", vbOKOnly + vbInformation, "Call Date"
        Range("H10").Select
        CheckEntry = False
        Exit Function

    End If
    
    If Trim(shForm.Range("H11").Value) = "" Then

        MsgBox "Transaction ID can't be blank.", vbOKOnly + vbInformation, "Transaction ID"
        Range("H11").Select
        CheckEntry = False
        Exit Function

    End If

    
    '3rd Section

    If Trim(shForm.Range("L9").Value) = "" Then

        MsgBox "Auditor's Name can't be blank.", vbOKOnly + vbInformation, "Auditor's Name"
        Range("L9").Select
        CheckEntry = False
        Exit Function

    End If

    If Trim(shForm.Range("L10").Value) = "" Then

        MsgBox "Audit Date can't be blank.", vbOKOnly + vbInformation, "Audit Date"
        Range("L10").Select
        CheckEntry = False
        Exit Function

    End If

    If Trim(shForm.Range("L11").Value) = "" Then

        MsgBox "Please select 'Feedback Shared' Yes or No from the drop-down.", vbOKOnly + vbInformation, "Feedback Shared"
        Range("L11").Select
        CheckEntry = False
        Exit Function

    End If

    
    'Category Validatin

    'Greeting & Closing Script - 1

    'Did the agent say thank you for calling or apply a local greeting?

    If Trim(shForm.Range("J32").Value) = "" Or (Trim(shForm.Range("J32").Value) <> "Yes" _
    And Trim(shForm.Range("J32").Value) <> "No" And Trim(shForm.Range("J32").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Greeting - Parameter 1 "
        Range("J32").Select
        CheckEntry = False
        Exit Function

    End If

    'Did agent offer further assistance before closing the call ?

    If Trim(shForm.Range("J33").Value) = "" Or (Trim(shForm.Range("J33").Value) <> "Yes" _
    And Trim(shForm.Range("J33").Value) <> "No" And Trim(shForm.Range("J33").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Greeting - Parameter 2 "
        Range("J33").Select
        CheckEntry = False
        Exit Function

    End If

    'Did the agent follow appropriate closing procedure ?

    If Trim(shForm.Range("J34").Value) = "" Or (Trim(shForm.Range("J34").Value) <> "Yes" _
    And Trim(shForm.Range("J34").Value) <> "No" And Trim(shForm.Range("J34").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Greeting - Parameter 3 "
        Range("J34").Select
        CheckEntry = False
        Exit Function

    End If


    'Courtesy & Communication -2

    'Did agent sound polite and courteous on the call ?

    If Trim(shForm.Range("J38").Value) = "" Or (Trim(shForm.Range("J38").Value) <> "Yes" _
    And Trim(shForm.Range("J38").Value) <> "No" And Trim(shForm.Range("J38").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Courtesy & Communication - Parameter 1 "
        Range("J38").Select
        CheckEntry = False
        Exit Function

    End If

    'Did agent empathize with customer's issue ?

    If Trim(shForm.Range("J39").Value) = "" Or (Trim(shForm.Range("J39").Value) <> "Yes" _
    And Trim(shForm.Range("J39").Value) <> "No" And Trim(shForm.Range("J39").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Courtesy & Communication - Parameter 2 "
        Range("J39").Select
        CheckEntry = False
        Exit Function

    End If

    'Did agent modulate his pitch and volume according to customer's ?

    If Trim(shForm.Range("J40").Value) = "" Or (Trim(shForm.Range("J40").Value) <> "Yes" _
    And Trim(shForm.Range("J40").Value) <> "No" And Trim(shForm.Range("J40").Value) <> "N/A") Then


        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Courtesy & Communication - Parameter 3 "
        Range("J40").Select
        CheckEntry = False
        Exit Function

    End If

    'Dead air for less than 15 secs

    If Trim(shForm.Range("J41").Value) = "" Or (Trim(shForm.Range("J41").Value) <> "Yes" _
    And Trim(shForm.Range("J41").Value) <> "No" And Trim(shForm.Range("J41").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Courtesy & Communication - Parameter 4 "
        Range("J41").Select
        CheckEntry = False
        Exit Function

    End If

    'Did the agent follow the correct Hold procedure ?

    If Trim(shForm.Range("J42").Value) = "" Or (Trim(shForm.Range("J42").Value) <> "Yes" _
    And Trim(shForm.Range("J42").Value) <> "No" And Trim(shForm.Range("J42").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Courtesy & Communication - Parameter 5 "
        Range("J42").Select
        CheckEntry = False
        Exit Function

    End If


    'Issue Resolution - 3


    'Did the agent authenticate customer and confirm the issue?

    If Trim(shForm.Range("J46").Value) = "" Or (Trim(shForm.Range("J46").Value) <> "Yes" _
    And Trim(shForm.Range("J46").Value) <> "No" And Trim(shForm.Range("J46").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Resolution - Parameter 1"
        Range("J46").Select
        CheckEntry = False
        Exit Function

    End If

    'Did the agent do effective probing on the call?

    If Trim(shForm.Range("J47").Value) = "" Or (Trim(shForm.Range("J47").Value) <> "Yes" _
    And Trim(shForm.Range("J47").Value) <> "No" And Trim(shForm.Range("J47").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Resolution - Parameter 2"
        Range("J47").Select
        CheckEntry = False
        Exit Function

    End If

    'Did the agent provide accurate and complete resolution on the call?

    If Trim(shForm.Range("J48").Value) = "" Or (Trim(shForm.Range("J48").Value) <> "Yes" _
    And Trim(shForm.Range("J48").Value) <> "No" And Trim(shForm.Range("J48").Value) <> "N/A") Then
        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Resolution - Parameter 3"
        Range("J48").Select
        CheckEntry = False
        Exit Function

    End If

    'Did the agent provide clear understanding of the issue to the customer?

    If Trim(shForm.Range("J49").Value) = "" Or (Trim(shForm.Range("J49").Value) <> "Yes" _
    And Trim(shForm.Range("J49").Value) <> "No" And Trim(shForm.Range("J49").Value) <> "N/A") Then
        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Resolution - Parameter 4"
        Range("J49").Select
        CheckEntry = False
        Exit Function

    End If

    'Did the agent process the request as promised to the customer?

    If Trim(shForm.Range("J50").Value) = "" Or (Trim(shForm.Range("J50").Value) <> "Yes" _
    And Trim(shForm.Range("J50").Value) <> "No" And Trim(shForm.Range("J50").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Resolution - Parameter 5"
        Range("J50").Select
        CheckEntry = False
        Exit Function

    End If


    'Case Documentation - 4

    'Agent documented the case correctly

    If Trim(shForm.Range("J54").Value) = "" Or (Trim(shForm.Range("J54").Value) <> "Yes" _
    And Trim(shForm.Range("J54").Value) <> "No" And Trim(shForm.Range("J54").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Documentation - Parameter 1"
        Range("J54").Select
        CheckEntry = False
        Exit Function

    End If

    'Agent escalated the case to the right department with all relevant details

    If Trim(shForm.Range("J55").Value) = "" Or (Trim(shForm.Range("J55").Value) <> "Yes" _
    And Trim(shForm.Range("J55").Value) <> "No" And Trim(shForm.Range("J55").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Documentation - Parameter 2"
        Range("J55").Select
        CheckEntry = False
        Exit Function

    End If


    'Process Compliance - 5
    'Did agent offer discount against SOP without prior approval from TL?

    If Trim(shForm.Range("J59").Value) = "" Or (Trim(shForm.Range("J59").Value) <> "Yes" _
    And Trim(shForm.Range("J59").Value) <> "No" And Trim(shForm.Range("J59").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Process Compliance - Parameter 1 "
        Range("J59").Select
        CheckEntry = False
        Exit Function

    End If

    'Did the agent modified case fields to avoid survey going to the customer?

    If Trim(shForm.Range("J60").Value) = "" Or (Trim(shForm.Range("J60").Value) <> "Yes" _
    And Trim(shForm.Range("J60").Value) <> "No" And Trim(shForm.Range("J60").Value) <> "N/A") Then

        MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Process Compliance - Parameter 2 "
        Range("J60").Select
        CheckEntry = False
        Exit Function

    End If


    ' Feedback Shared

    If shForm.optYes.Value = False And shForm.optNo.Value = False Then

        MsgBox "Please select 'Send Feedback Mail' Option (Yes/No).", vbOKOnly + vbInformation, "Send Feedback"
        Range("B13").Select
        CheckEntry = False
        Exit Function

    End If



End Function

VBA Function to Transfer the Audit data for Audit Dump

Sub Transfer()

    Dim iRow As Integer

    Dim shForm As Object
    Dim shDump As Object

    Set shForm = ThisWorkbook.Sheets("Form")
    Set shDump = ThisWorkbook.Sheets("Audit Dump")

    shDump.Select

    iRow = shDump.Range("A" & Application.Rows.Count).End(xlUp).Row + 1 'Identify the last row

    
    
    With shDump
    
    ''Employee & Other Details
    
    .Cells(iRow, 1).Value = shForm.Range("D9").Value 'Employee ID
    .Cells(iRow, 2).Value = shForm.Range("D10").Value ' Employee Name
    .Cells(iRow, 3).Value = shForm.Range("D11").Value 'Employee Email ID

    
    .Cells(iRow, 4).Value = shForm.Range("H9").Value 'Client Name
    .Cells(iRow, 5).Value = shForm.Range("H10").Value 'Call Date
    .Cells(iRow, 6).Value = shForm.Range("H11").Value 'Transaction ID

    
    .Cells(iRow, 7).Value = shForm.Range("L9").Value 'Auditor's Name
    .Cells(iRow, 8).Value = shForm.Range("L10").Value 'Audit Date
    .Cells(iRow, 9).Value = shForm.Range("L11").Value 'Feedback Shared

    ' Audit Score Summary
    
    .Cells(iRow, 10).Value = shForm.Range("F24").Value 'Overall Appliable Points
    .Cells(iRow, 11).Value = shForm.Range("H24").Value 'Overall Earned Points
    .Cells(iRow, 12).Value = shForm.Range("J24").Value 'Overall Score

    

    'Greeting -1

    .Cells(iRow, 13).Value = shForm.Range("J32").Value 'Did the agent say thank you for calling or apply a local greeting? - Audit Result
    .Cells(iRow, 14).Value = shForm.Range("N32").Value 'Did the agent say thank you for calling or apply a local greeting? - Applicable Points
    .Cells(iRow, 15).Value = shForm.Range("O32").Value 'Did the agent say thank you for calling or apply a local greeting? - Earned Points
    .Cells(iRow, 16).Value = shForm.Range("L32").Value 'Did the agent say thank you for calling or apply a local greeting? - Comment


    .Cells(iRow, 17).Value = shForm.Range("J33").Value 'Did agent offer further assistance before disconnecting the chat? - Audit Result
    .Cells(iRow, 18).Value = shForm.Range("N33").Value 'Did agent offer further assistance before disconnecting the chat? - Applicable Points
    .Cells(iRow, 19).Value = shForm.Range("O33").Value 'Did agent offer further assistance before disconnecting the chat? - Earned Points
    .Cells(iRow, 20).Value = shForm.Range("L33").Value 'Did agent offer further assistance before disconnecting the chat? - Comment

    .Cells(iRow, 21).Value = shForm.Range("J34").Value 'Did the agent follow appropriate closing procedure? - Audit Result
    .Cells(iRow, 22).Value = shForm.Range("N34").Value 'Did the agent follow appropriate closing procedure? - Applicable Points
    .Cells(iRow, 23).Value = shForm.Range("O34").Value 'Did the agent follow appropriate closing procedure? - Earned Points
    .Cells(iRow, 24).Value = shForm.Range("L34").Value 'Did the agent follow appropriate closing procedure? - Comment


   'Courtesy & Communication - 2

    .Cells(iRow, 25).Value = shForm.Range("J38").Value 'Did agent sound polite and courteous on the call ? - Audit Result
    .Cells(iRow, 26).Value = shForm.Range("N38").Value 'Did agent sound polite and courteous on the call ? - Applicable Points
    .Cells(iRow, 27).Value = shForm.Range("O38").Value 'Did agent sound polite and courteous on the call ? - Earned Points
    .Cells(iRow, 28).Value = shForm.Range("L38").Value 'Did agent sound polite and courteous on the call ? - Comment


    .Cells(iRow, 29).Value = shForm.Range("J39").Value 'Did agent empathize with customer's issue ? - Audit Result
    .Cells(iRow, 30).Value = shForm.Range("N39").Value 'Did agent empathize with customer's issue ? - Applicable Points
    .Cells(iRow, 31).Value = shForm.Range("O39").Value 'Did agent empathize with customer's issue ? - Earned Points
    .Cells(iRow, 32).Value = shForm.Range("L39").Value 'Did agent empathize with customer's issue ? - Comment

    .Cells(iRow, 33).Value = shForm.Range("J40").Value 'Did agent modulate his pitch and volume according to customer's ? - Audit Result
    .Cells(iRow, 34).Value = shForm.Range("N40").Value 'Did agent modulate his pitch and volume according to customer's ? - Applicable Points
    .Cells(iRow, 35).Value = shForm.Range("O40").Value 'Did agent modulate his pitch and volume according to customer's ? - Earned Points
    .Cells(iRow, 36).Value = shForm.Range("L40").Value 'Did agent modulate his pitch and volume according to customer's ? - Comment

    .Cells(iRow, 37).Value = shForm.Range("J41").Value 'Did the agent follow the call transfer protocol? - Audit Result
    .Cells(iRow, 38).Value = shForm.Range("N41").Value 'Did the agent follow the call transfer protocol? - Applicable Points
    .Cells(iRow, 39).Value = shForm.Range("O41").Value 'Did the agent follow the call transfer protocol? - Earned Points
    .Cells(iRow, 40).Value = shForm.Range("L41").Value 'Did the agent follow the call transfer protocol? - Comment

    .Cells(iRow, 41).Value = shForm.Range("J42").Value 'Did the agent follow the correct Hold procedure ? - Audit Result
    .Cells(iRow, 42).Value = shForm.Range("N42").Value 'Did the agent follow the correct Hold procedure ? - Applicable Points
    .Cells(iRow, 43).Value = shForm.Range("O42").Value 'Did the agent follow the correct Hold procedure ? - Earned Points
    .Cells(iRow, 44).Value = shForm.Range("L42").Value 'Did the agent follow the correct Hold procedure ?- Comment


    'Issue Resolution - 3


    .Cells(iRow, 45).Value = shForm.Range("J46").Value 'Did the agent authenticate customer and confirm the issue? - Audit Result
    .Cells(iRow, 46).Value = shForm.Range("N46").Value 'Did the agent authenticate customer and confirm the issue? - Applicable Points
    .Cells(iRow, 47).Value = shForm.Range("O46").Value 'Did the agent authenticate customer and confirm the issue? - Earned Points
    .Cells(iRow, 48).Value = shForm.Range("L46").Value 'Did the agent authenticate customer and confirm the issue? - Comment

    .Cells(iRow, 49).Value = shForm.Range("J47").Value 'Did the agent do effective probing on the call? - Audit Result
    .Cells(iRow, 50).Value = shForm.Range("N47").Value 'Did the agent do effective probing on the call? - Applicable Points
    .Cells(iRow, 51).Value = shForm.Range("O47").Value 'Did the agent do effective probing on the call? - Earned Points
    .Cells(iRow, 52).Value = shForm.Range("L47").Value 'Did the agent do effective probing on the call? - Comment

    .Cells(iRow, 53).Value = shForm.Range("J48").Value 'Did the agent provide accurate and complete resolution on the call?  - Audit Result
    .Cells(iRow, 54).Value = shForm.Range("N48").Value 'Did the agent provide accurate and complete resolution on the call?  - Applicable Points
    .Cells(iRow, 55).Value = shForm.Range("O48").Value 'Did the agent provide accurate and complete resolution on the call?  - Earned Points
    .Cells(iRow, 56).Value = shForm.Range("L48").Value 'Did the agent provide accurate and complete resolution on the call?  - Comment

    .Cells(iRow, 57).Value = shForm.Range("J49").Value 'Did the agent provide clear understanding of the issue to the customer? - Audit Result
    .Cells(iRow, 58).Value = shForm.Range("N49").Value 'Did the agent provide clear understanding of the issue to the customer? - Applicable Points
    .Cells(iRow, 59).Value = shForm.Range("O49").Value 'Did the agent provide clear understanding of the issue to the customer? - Earned Points
    .Cells(iRow, 60).Value = shForm.Range("L49").Value 'Did the agent provide clear understanding of the issue to the customer? - Comment

    .Cells(iRow, 61).Value = shForm.Range("J50").Value 'Did the agent process the request as promised to the customer? - Audit Result
    .Cells(iRow, 62).Value = shForm.Range("N50").Value 'Did the agent process the request as promised to the customer? - Applicable Points
    .Cells(iRow, 63).Value = shForm.Range("O50").Value 'Did the agent process the request as promised to the customer? - Earned Points
    .Cells(iRow, 64).Value = shForm.Range("L50").Value 'Did the agent process the request as promised to the customer? - Comment

    'Case Documentation

    .Cells(iRow, 65).Value = shForm.Range("J54").Value 'Agent documented the case correctly - Audit Result
    .Cells(iRow, 66).Value = shForm.Range("N54").Value 'Agent documented the case correctly - Applicable Points
    .Cells(iRow, 67).Value = shForm.Range("O54").Value 'Agent documented the case correctly - Earned Points
    .Cells(iRow, 68).Value = shForm.Range("L54").Value 'Agent documented the case correctly - Comment

    .Cells(iRow, 69).Value = shForm.Range("J55").Value 'Agent escalated the case to the right department with all relevant details - Audit Result
    .Cells(iRow, 70).Value = shForm.Range("N55").Value 'Agent escalated the case to the right department with all relevant details - Applicable Points
    .Cells(iRow, 71).Value = shForm.Range("O55").Value 'Agent escalated the case to the right department with all relevant details - Earned Points
    .Cells(iRow, 72).Value = shForm.Range("L55").Value 'Agent escalated the case to the right department with all relevant details - Comment

    'Process Compliance

    .Cells(iRow, 73).Value = shForm.Range("J59").Value 'Did agent offer discount against SOP without prior approval from TL? - Audit Result
    .Cells(iRow, 74).Value = shForm.Range("N59").Value 'Did agent offer discount against SOP without prior approval from TL? - Applicable Points
    .Cells(iRow, 75).Value = shForm.Range("O59").Value 'Did agent offer discount against SOP without prior approval from TL? - Earned Points
    .Cells(iRow, 76).Value = shForm.Range("L59").Value 'Did agent offer discount against SOP without prior approval from TL? - Comment

    .Cells(iRow, 77).Value = shForm.Range("J60").Value 'Did the agent modified case fields to avoid CSAT going to the customer? - Audit Result
    .Cells(iRow, 78).Value = shForm.Range("N60").Value 'Did the agent modified case fields to avoid CSAT going to the customer? - Applicable Points
    .Cells(iRow, 79).Value = shForm.Range("O60").Value 'Did the agent modified case fields to avoid CSAT going to the customer? - Earned Points
    .Cells(iRow, 80).Value = shForm.Range("L60").Value 'Did the agent modified case fields to avoid CSAT going to the customer? - Comment

    'Others
    .Cells(iRow, 81).Value = shForm.Range("L11").Value 'Feedback Shared Yes/No
    .Cells(iRow, 82).Value = shForm.Range("B64").Value 'Feedback/Remarks
    .Cells(iRow, 83).Value = Application.UserName 'Updated By
    .Cells(iRow, 84).Value = [Now()] 'Updated Date

    .Range("A2").Select
    
    End With


End Sub

VBA Function to get confirmation before submitting the data and call all the required functions before resetting the form

' Procedure to call all the functions to transfer the data

Sub SaveData()
    
    Dim sAuditorName As String
    Dim shForm As Object
    
    Dim iMsg As Integer

    iMsg = MsgBox("Do you want to submit this form?", vbYesNo + vbQuestion, "Submit Confirmation")

    If iMsg = vbNo Then Exit Sub
       

    Set shForm = ThisWorkbook.Sheets("Form")

    If CheckEntry = False Then

        Exit Sub
    Else

        sAuditorName = shForm.Range("L9").Value

        Application.StatusBar = "Saving Data..."
        Call Transfer
        
        Application.StatusBar = "Drafting Email..."
        Call Feedback_Email
        
        Application.StatusBar = "Reseting Form..."
        Call InitializeSheet
        
        Application.StatusBar = "Done!"
        Application.StatusBar = False

        If Trim(sAuditorName) <> "" Then

            shForm.Range("L9").Value = Trim(sAuditorName)

        End If

        MsgBox "Call audit score and summary updated successfully!"
        ThisWorkbook.Save

        Exit Sub

    End If


End Sub

This is all about the Call Center Transaction Monitoring Form in Excel and VBA. Worksheet, Workbook and VBA password is thedatalabs .

Secure Payments

Please watch our YouTube tutorials. Thanks!

1 COMMENT

  1. Excellent Video…. Thank you so much I am searching since long time for video like this. thanks lot…

    please can you guide me how to design tables for a employee hiring and transfer maintenance DB in various branches of a company for employees. please.

    1. DB should allow to assign employees to vacant posts, if already assigned it should show it is already assigned.

    2.Once employee is assigned to a Post then status of post should change to filled from the date of appointment to till the date of removal/resignation/transfer of employee.

    3.If employee resigned and transferred, then the status of post should become Vacant and then it should allow us to assign other employees to that post from the date of vacant

    4.DB should record various employees worked in a post for different periods.

    5.DB should show us no of posts filled and no of post vacant at any time.

    please guide or make video on this please… I am desperately needed this please.

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