This forum is closed to new posts and
responses. Individual names altered for privacy purposes. The information contained in this website is provided for informational purposes only and should not be construed as a forum for customer support requests. Any customer support requests should be directed to the official HCL customer support channels below:
Sub CreateSheets(xlApp As Variant, strName1 As String, strName2 As String, strName3 As String, strName4 As String)
Dim xlsheet As Variant
Dim xlsheet2 As Variant
Dim xlsheet3 As Variant
Dim xlsheet4 As Variant
'this will create the worksheets beforewe can use them
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
xlSheet.Activate
xlsheet.Name = strName1
xlsheet.Columns("A:Q").Select
xlApp.Selection.Locked = False
Set xlsheet3 = xlApp.Workbooks(1).Worksheets(3)
xlSheet3.Activate
xlsheet3.Name = strName3
Set xlsheet4 = xlApp.Workbooks(1).Worksheets.Add(Null,xlsheet3)
xlSheet4.Activate
xlsheet4.Name = strName4
End Sub
Sub SetUpSheet1( xlApp As Variant, strName2 As String, strName3 As String )
' Purpose:
' - set up the first worksheet.
'
Dim vDate As Variant
Dim xlsheet As Variant
Dim intStart As Integer
Dim intTotal As Integer
Dim x As Integer
Dim strStartRow As String
Dim strEndRow As String
vDate = Date
xlApp.StatusBar = "Setting up the summary worksheet. Please be patient..."
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
xlsheet.Activate
'this will create the headings for the various cloumns.
xlsheet.Cells(1,1).Value = "SUPPORT ENGINEER'S INCENTIVE SCHEME - STATUS REPORT OF - "& vDate
xlsheet.Cells(3,1).Value = Me.YearOfInterest
xlsheet.Cells(3,2).Value = Me.QuarterOfInterest
xlsheet.Cells(3,3).Value = Me.RegionOfInterest
xlsheet.Cells(3,7).Value = "CSat multiplier"
xlsheet.Cells(3,8).Value = "1"
xlsheet.Cells(x ,7).Value = "Csat Level"
xlsheet.Cells(x ,8).Value = "Multiplier"
x = x + 1
xlsheet.Cells(x ,7).Value = "8.5 or less"
xlsheet.Cells(x ,8).Value = "1"
x = x + 1
xlsheet.Cells(x ,7).Value = "8.5 to 9.0"
xlsheet.Cells(x ,8).Value = "1.2"
x = x + 1
xlsheet.Cells(x ,7).Value = "Above 9.0"
xlsheet.Cells(x ,8).Value = "1.5"
x = x + 2
xlsheet.Cells(x, 2).Value = "INSTRUCTIONS FOR USE"
x = x + 1
xlsheet.Cells(x, 2).Value = "1. Enter the appropriate multiplier for the team's CSAT score in the cell above."
x = x + 1
xlsheet.Cells(x, 2).Value = "2. Specify N in the To be paid column (P) on the Details sheet to stop the bonus being paid for that quote. Please enter why into Reason Not Paid (Q)."
x = x + 1
xlsheet.Cells(x, 2).Value = "3. To stop the energy rate being used for bonus calculations, go back to the view, select the quote(s) and run ""Energy Related\No"". Re-run this report."
x = x + 1
xlsheet.Cells(x, 2).Value = "4. To use the energy rate for the bonus calculation, go back to the view, select the quote(s) and run ""Energy Related\Yes"". Re-run this report."
x = x + 1
xlsheet.Cells(x, 2).Value = "5. Remember to select ALL quotes from the Incentive Scheme view being paid and run ""Mark Bonus Paid."" "
x = x + 2
xlsheet.Cells(x, 2).Value = "WARNING"
x = x + 1
xlsheet.Cells(x, 2).Value = "Do not delete lines from this worksheet as it causes problems on the Payroll sheet. Delete any lines with a 0 payment on the payroll sheet"
Call FormatSheet1(xlApp, intTotal)
End Sub
Sub FormatSheet1(xlApp As Variant, intTotal As Integer)
Dim xlsheet As Variant
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
xlApp.StatusBar = "Formating Sheet 1. Please be patient..."
'select the title rows and make them bold
xlApp.Range(xlsheet.Cells(1,1), xlsheet.Cells(Me.intSheet1,10)).Select
xlApp.Selection.Font.Bold = True
'select the totals and make it bold
xlSheet.Rows(intTotal).Select
xlApp.Selection.Font.Bold = True
'select the engineer number
'now auto size the sheet
xlApp.Range(xlsheet.Cells(Me.intSheet1,1), xlsheet.Cells(intTotal,10)).Select
xlApp.Selection.Font.Name = "Arial"
xlApp.Selection.Font.Size = 9
xlApp.Selection.Columns.AutoFit
xlApp.Range("D:J").Select
xlApp.Selection.NumberFormat = "#,##0.00"
End Sub
Sub SetUpSheet2(xlApp As Variant)
Dim intQuotes As Integer
Dim intStartRow As Integer
Dim x As Integer
Dim xlsheet As Variant
xlApp.StatusBar = "Setting up the front worksheet. Please be patient..."
Set xlsheet = xlApp.Workbooks(1).Worksheets(2)
xlsheet.Activate
Forall elements In Me.lstEngineer
Call elements.ExtractQuoteDetails (xlApp, xlsheet, Listtag (elements ), x )
x = x + elements.NumberOfQuotes
End Forall
Call FormatSheet2(xlApp,Me.intSheet2, x)
End Sub
Sub FormatSheet2(xlApp As Variant, intHeading As Integer, intEndRow As Integer)
' Purpose
' - format the second worksheet
'
Dim xlsheet As Variant
Set xlsheet = xlApp.Workbooks(1).Worksheets(2)
xlApp.StatusBar = "Formating Sheet 2. Please be patient..."
The rest of the code
Return to top
. . . . The rest of the code (~Ethan Cisvelum... 4.Jan.17) 
