Need to stop my code from 'stopping' after "Else: Exit Sub"

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
322
Office Version
  1. 2010
Platform
  1. Windows
I am having a problem where the module running 'stops' at the wrong time.

here is what the code is for (some background on my workbook):

I have a userform where you can select a date range as well as multiple categories that you want to have a tally executed for on those categories in the main spreadsheet between the 2 dates that are selected.

There is also a "select all categories" box as an option.

If the user selects a date range and the "select all catagories", the script runs fine and it captures all the codes that its searching for, copies them to the designated worksheet, pastes them, and then activates that worksheet and ends. Thats how its supposed to work.

However, if the user randomly selects individual checkboxes (representing the codes that it will be searching for) (in lieu of choose the 'select all categories', the code does not run the way it should.

It captures and tally's all the data just fine, but it stops short of populating that data on the designated worksheet where it can be viewed, and instead gets 'hung up' on the tally page (which isn't supposed to be seen and just serves as the go-between sheet where the captured data is temporarily placed so the tallys can be calculated and then copied and pasted onto the designated worksheet (which is titled "REPORTS"

the code- what is shown below are the individual procedures. there are FOUR of these representing the four different categories where the user can select different checkboxes in, plus a FIFTH one that represents the totals from all the other ones... kind of a 'summary' column where the tallys are shown.

Code:
Dim jP As Variant
Dim lP As Variant
Dim mP As Variant
Dim nP As Variant
Dim cntRowsJ As Long
Dim cntRowsK As Long
Dim cntRowsL As Long
Dim cntRowsM As Long
Dim cntRowsN As Long
cntRowsJ = ActiveWorkbook.Worksheets("ANALYTICS").Range("J65536").End(xlUp).Row
cntRowsK = ActiveWorkbook.Worksheets("ANALYTICS").Range("K65536").End(xlUp).Row
cntRowsL = ActiveWorkbook.Worksheets("ANALYTICS").Range("L65536").End(xlUp).Row
cntRowsM = ActiveWorkbook.Worksheets("ANALYTICS").Range("M65536").End(xlUp).Row
cntRowsN = ActiveWorkbook.Worksheets("ANALYTICS").Range("N65536").End(xlUp).Row
'**********************************************************************************************************************************************
'**********************************************************************************************************************************************
For Each jP In Range(Cells(1, 10), Cells(cntRowsJ, 10))
' TOTAL INCIDENT TALLY
' COPY THE ROWS IN THE "TOTAL" COLUMN (ON THE ANALYTICS SHEET) AND PASTE ONTO THE TOTAL COLUMN (A2) ON THE "REPORTS" WORKSHEET:
    If cntRowsJ >= 0 Then
    
    'copies just the rows in column J and pastes them on the REPORTS worksheet starting with cell A2.
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
    ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 10), Cells(cntRowsJ, 10)).Select
    Selection.Copy
    ActiveWorkbook.Worksheets("REPORTS").Activate
    ActiveWorkbook.Worksheets("REPORTS").Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
   
    Else: Exit Sub

    End If
Next
'**********************************************************************************************************************************************
For Each kP In Range(Cells(1, 11), Cells(cntRowsK, 11))
' CUSTOMER SERVICE INCIDENT TALLY
' COPY THE ROWS IN THE "CUSTOMER SERVICE (CSR)" COLUMN (ON THE ANALYTICS SHEET) AND PASTE ONTO THE TOTAL COLUMN (D2) ON THE "REPORTS" WORKSHEET:
    If cntRowsK >= 0 Then
    
    'copies just the rows in column K and pastes them on the REPORTS worksheet starting with cell D2.
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
    ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 11), Cells(15, 11)).Select
    Selection.Copy
    ActiveWorkbook.Worksheets("REPORTS").Activate
    ActiveWorkbook.Worksheets("REPORTS").Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWorkbook.Worksheets("ANALYTICS").Activate

    Else: Exit Sub

    End If
Next
'**********************************************************************************************************************************************
For Each lP In Range(Cells(1, 12), Cells(cntRowsL, 12))
' INTERNAL SEATEX INCIDENT TALLY
' COPY THE ROWS IN THE "SEATEX INTERNAL" COLUMN (ON THE ANALYTICS SHEET) AND PASTE ONTO THE TOTAL COLUMN (A2) ON THE "REPORTS" WORKSHEET:
    If cntRowsL >= 0 Then
    
    'copies just the rows in column L and pastes them on the REPORTS worksheet starting with cell H2.
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
    ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 12), Cells(15, 12)).Select
    Selection.Copy
    ActiveWorkbook.Worksheets("REPORTS").Activate
    ActiveWorkbook.Worksheets("REPORTS").Range("H2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
   
    Else: Exit Sub

    End If
Next
'**********************************************************************************************************************************************
For Each mP In Range(Cells(1, 13), Cells(cntRowsM, 13))
' MISCELLANEOUS INCIDENT TALLY
' COPY THE ROWS IN THE "MISCELLANEOUS COLUMN (ON THE ANALYTICS SHEET) AND PASTE ONTO THE TOTAL COLUMN (A2) ON THE "REPORTS" WORKSHEET:
    If cntRowsM >= 0 Then
    
    'copies just the rows in column M and pastes them on the REPORTS worksheet starting with cell I2.
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
    ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 13), Cells(15, 13)).Select
    Selection.Copy
    ActiveWorkbook.Worksheets("REPORTS").Activate
    ActiveWorkbook.Worksheets("REPORTS").Range("I2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
   
    Else: Exit Sub

    End If
Next
'**********************************************************************************************************************************************
For Each nP In Range(Cells(1, 14), Cells(cntRowsN, 14))
' E-H-S INCIDENT TALLY
' COPY THE ROWS IN THE "EHS" COLUMN (ON THE ANALYTICS SHEET) AND PASTE ONTO THE TOTAL COLUMN (A2) ON THE "REPORTS" WORKSHEET:
    If cntRowsN >= 0 Then
    
    'copies just the rows in column N and pastes them on the REPORTS worksheet starting with cell J2.
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
    ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 14), Cells(15, 14)).Select
    Selection.Copy
    ActiveWorkbook.Worksheets("REPORTS").Activate
    ActiveWorkbook.Worksheets("REPORTS").Range("J2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
   
    Else: Exit Sub

    End If
Next

Next, the code is SUPPOSED to complete with taking the user to the REPORTS worksheet which is now populated with the tally totals and information... (but it only does this when the "select all" checkbox was selected, it gets 'hung up' on the tally worksheet ("ANALYTICS") and doesn't redirect back to the REPORTS worksheet...)

Code:
UNFILTER THE "SEATEX INCIDENT LOG" WORKSHEET FROM WHERE IT WAS PREVISOULY FILTERED TO SHOW ONLY THE ROWS THAT FALL BETWEEN 2 DATES
'**********************************************************************************************************************************************
With ws
    ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
    ActiveSheet.Range(Cells(17, 1), Cells(rCol, 49)).AutoFilter Field:=2
    ActiveWorkbook.Worksheets("Seatex Incident Log").Range(Cells(rCol, 1), Cells(rCol, 1)).Select
End With
'REDIRECT FOCUS BACK TO THE "REPORTS" WORKSHEET
'**********************************************************************************************************************************************
        ActiveWorkbook.Worksheets("REPORTS").Activate
        ActiveWorkbook.Worksheets("REPORTS").Visible = True
        ActiveWorkbook.Worksheets("REPORTS").Range("A18").Select

With ActiveWorkbook.Worksheets("REPORTS")
    ActiveWindow.ScrollIntoView Left:=0, Top:=0, Width:=100, Height:=100
    Cells(18, 1).Select
    ActiveWindow.ScrollColumn = ActiveCell.Column
End With

bm_Error_norecords:
'MsgBox "Sorry, no Incidents were found"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

bFlag = True
Unload Me

End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
322
Office Version
  1. 2010
Platform
  1. Windows
a few pictures to show what it does and what I am trying to get it to do...

Here is the userform where the selections can be made by the user:
Form1.PNG

the user selects a date range and chooses to either select individual checkboxes or they can select "show all incidents"
Form2.PNG

The module runs perfect if the 'show all incidents' is selected and it correctly takes the user to the summary worksheet: ("REPORTS")
Form3.PNG

but this is not the case if the user chooses to select some individual checkboxes in lieu of the "show all incidents"
Form4.PNG

If that is the case, then instead of completing with the REPORTS worksheet shown (as shown in third picture above), then it gets 'hung up' on the worksheet "ANALYTICS" page and doesn;t redirect back to the "REPORTS" page, which is what I need it to do....
Form5.PNG
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
322
Office Version
  1. 2010
Platform
  1. Windows
its getting stranger... I;ve pinpointed the place where it stops running the code and just ends.

Its right here(?)
Form6.PNG

for the life of me I cannot figure out why it would stop right at this point (I get the message box "Here 2" that I put in as I was trying to track down where teh problem is) and then no others.

However, if the "show all incidents" is checked, it shows all of the test message boxs that I have put in the code... this is really frustrating me now!
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
322
Office Version
  1. 2010
Platform
  1. Windows
Also, here is the enture code for the userform module that is giving me fits (sorry, I should of just posted all of it with my orignal post.)

Code:
Private bFlag As Boolean
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = 0 Then
    '==========
Worksheets("Seatex Incident Log").Activate
    '==========
  End If
End Sub
' *****************************************************************************************************
' FORMATTING ALL OF THE CHECKBOXES ON THE FORM
' *****************************************************************************************************

' CODE FOR CHANGING THE CHECKBOXES TO YELLOW WHEN THEY ARE CHECKED
'REFERENCE:
'           EHS =               lblEHS_1
'           CSR =               lblCSR_1
'           INTERNAL =          lblSEA_1
'           MISCELLANEOUS =     lblMISC_1
' *****************************************************************************************************
' CUSTOMER SERVICE GROUP
' *****************************************************************************************************
' LABEL ISSUES BACK COLOR: &H00FFC0C0&
Private Sub chkCSR_Click()
If chkCSR = True Then Me.lblCSR0o.BackColor = &HFFFF&
If chkCSR = False Then Me.lblCSR0o.BackColor = &HEFD5C3
End Sub
' *************************** SUBGROUP: Labels
Private Sub chkCSR1A_Click()
If chkCSR1A = True Then Me.lblCSR1A2.BackColor = &HFFFF&
If chkCSR1A = False Then Me.lblCSR1A2.BackColor = &HFFC0FF
If chkCSR1A = True Then Me.chkCSR21U.Value = True
If chkCSR1A = False And chkCSR2B = False And chkCSR3C = False And chkCSR4D = False Then Me.chkCSR21U.Value = False
End Sub
Private Sub chkCSR2B_Click()
If chkCSR2B = True Then Me.lblCSR2B2.BackColor = &HFFFF&
If chkCSR2B = False Then Me.lblCSR2B2.BackColor = &HFFC0FF
If chkCSR2B = True Then Me.chkCSR21U.Value = True
If chkCSR1A = False And chkCSR2B = False And chkCSR3C = False And chkCSR4D = False Then Me.chkCSR21U.Value = False
End Sub
Private Sub chkCSR3C_Click()
If chkCSR3C = True Then Me.lblCSR3C2.BackColor = &HFFFF&
If chkCSR3C = False Then Me.lblCSR3C2.BackColor = &HFFC0FF
If chkCSR3C = True Then Me.chkCSR21U.Value = True
If chkCSR1A = False And chkCSR2B = False And chkCSR3C = False And chkCSR4D = False Then Me.chkCSR21U.Value = False
End Sub
Private Sub chkCSR4D_Click()
If chkCSR4D = True Then Me.lblCSR4D2.BackColor = &HFFFF&
If chkCSR4D = False Then Me.lblCSR4D2.BackColor = &HFFC0FF
If chkCSR4D = True Then Me.chkCSR21U.Value = True
If chkCSR1A = False And chkCSR2B = False And chkCSR3C = False And chkCSR4D = False Then Me.chkCSR21U.Value = False
End Sub
' *************************** SUBGROUP: Packaging
Private Sub chkCSR5E_Click()
If chkCSR5E = True Then Me.lblCSR5E2.BackColor = &HFFFF&
If chkCSR5E = False Then Me.lblCSR5E2.BackColor = &HC0E0FF
If chkCSR5E = True Then Me.chkCSR22V.Value = True
If chkCSR5E = False And chkCSR6F = False And chkCSR7G = False And chkCSR8H = False Then Me.chkCSR22V.Value = False
End Sub
Private Sub chkCSR6F_Click()
If chkCSR6F = True Then Me.lblCSR6F2.BackColor = &HFFFF&
If chkCSR6F = False Then Me.lblCSR6F2.BackColor = &HC0E0FF
If chkCSR6F = True Then Me.chkCSR22V.Value = True
If chkCSR6F = False And chkCSR5E = False And chkCSR7G = False And chkCSR8H = False Then Me.chkCSR22V.Value = False
End Sub
Private Sub chkCSR7G_Click()
If chkCSR7G = True Then Me.lblCSR7G2.BackColor = &HFFFF&
If chkCSR7G = False Then Me.lblCSR7G2.BackColor = &HC0E0FF
If chkCSR7G = True Then Me.chkCSR22V.Value = True
If chkCSR7G = False And chkCSR5E = False And chkCSR6F = False And chkCSR8H = False Then Me.chkCSR22V.Value = False
End Sub
Private Sub chkCSR8H_Click()
If chkCSR8H = True Then Me.lblCSR8H2.BackColor = &HFFFF&
If chkCSR8H = False Then Me.lblCSR8H2.BackColor = &HC0E0FF
If chkCSR8H = True Then Me.chkCSR22V.Value = True
If chkCSR8H = False And chkCSR5E = False And chkCSR6F = False And chkCSR7G = False Then Me.chkCSR22V.Value = False
End Sub
' *************************** SUBGROUP Product Condition
Private Sub chkCSR9I_Click()
If chkCSR9I = True Then Me.lblCSR9I2.BackColor = &HFFFF&
If chkCSR9I = False Then Me.lblCSR9I2.BackColor = &HFFFFC0
If chkCSR9I = True Then Me.chkCSR23W.Value = True
If chkCSR9I = False And chkCSR10J = False And chkCSR11K = False And chkCSR12L = False And chkCSR13M = False Then Me.chkCSR23W.Value = False
End Sub
Private Sub chkCSR10J_Click()
If chkCSR10J = True Then Me.lblCSR10J2.BackColor = &HFFFF&
If chkCSR10J = False Then Me.lblCSR10J2.BackColor = &HFFFFC0
If chkCSR10J = True Then Me.chkCSR23W.Value = True
If chkCSR10J = False And chkCSR9I = False And chkCSR11K = False And chkCSR12L = False And chkCSR13M = False Then Me.chkCSR23W.Value = False
End Sub
Private Sub chkCSR11K_Click()
If chkCSR11K = True Then Me.lblCSR11K2.BackColor = &HFFFF&
If chkCSR11K = False Then Me.lblCSR11K2.BackColor = &HFFFFC0
If chkCSR11K = True Then Me.chkCSR23W.Value = True
If chkCSR11K = False And chkCSR9I = False And chkCSR10J = False And chkCSR12L = False And chkCSR13M = False Then Me.chkCSR23W.Value = False
End Sub
Private Sub chkCSR12L_Click()
If chkCSR12L = True Then Me.lblCSR12L2.BackColor = &HFFFF&
If chkCSR12L = False Then Me.lblCSR12L2.BackColor = &HFFFFC0
If chkCSR12L = True Then Me.chkCSR23W.Value = True
If chkCSR12L = False And chkCSR9I = False And chkCSR10J = False And chkCSR11K = False And chkCSR13M = False Then Me.chkCSR23W.Value = False
End Sub
Private Sub chkCSR13M_Click()
If chkCSR13M = True Then Me.lblCSR13M2.BackColor = &HFFFF&
If chkCSR13M = False Then Me.lblCSR13M2.BackColor = &HFFFFC0
If chkCSR13M = True Then Me.chkCSR23W.Value = True
If chkCSR13M = False And chkCSR9I = False And chkCSR10J = False And chkCSR11K = False And chkCSR12L = False Then Me.chkCSR23W.Value = False
End Sub
' *************************** SUBGROUP Miscellaneous
Private Sub chkCSR28BB_Click()
If chkCSR28BB = True Then Me.lblCSR28BB2.BackColor = &HFFFF&
If chkCSR28BB = False Then Me.lblCSR28BB2.BackColor = &HFFAFD7
If chkCSR28BB = True Then Me.chkCSR24X.Value = True
'If chkCSR28BB = False And chkCSR15O = False And chkCSR16P = False And chkCSR17Q = False And chkCSR18R = False And chkCSR19S = False And chkCSR20T = False Then Me.chkCSR24X.Value = False
If chkCSR28BB = False And chkCSR29CC = False And chkCSR16P = False And chkCSR14N = False And chkCSR31EE = False And chkCSR32FF = False And chkCSR19S = False Then Me.chkCSR24X.Value = False
End Sub
Private Sub chkCSR29CC_Click()
If chkCSR29CC = True Then Me.lblCSR29CC2.BackColor = &HFFFF&
If chkCSR29CC = False Then Me.lblCSR29CC2.BackColor = &HFFAFD7
If chkCSR29CC = True Then Me.chkCSR24X.Value = True
If chkCSR28BB = False And chkCSR29CC = False And chkCSR16P = False And chkCSR14N = False And chkCSR31EE = False And chkCSR32FF = False And chkCSR19S = False Then Me.chkCSR24X.Value = False
End Sub
Private Sub chkCSR16P_Click()
If chkCSR16P = True Then Me.lblCSR16P2.BackColor = &HFFFF&
If chkCSR16P = False Then Me.lblCSR16P2.BackColor = &HFFAFD7
If chkCSR16P = True Then Me.chkCSR24X.Value = True
If chkCSR28BB = False And chkCSR29CC = False And chkCSR16P = False And chkCSR14N = False And chkCSR31EE = False And chkCSR32FF = False And chkCSR19S = False Then Me.chkCSR24X.Value = False
End Sub
Private Sub chkCSR14N_Click()
If chkCSR14N = True Then Me.lblCSR14N2.BackColor = &HFFFF&
If chkCSR14N = False Then Me.lblCSR14N2.BackColor = &HFFAFD7
If chkCSR14N = True Then Me.chkCSR24X.Value = True
If chkCSR28BB = False And chkCSR29CC = False And chkCSR16P = False And chkCSR14N = False And chkCSR31EE = False And chkCSR32FF = False And chkCSR19S = False Then Me.chkCSR24X.Value = False
End Sub
Private Sub chkCSR31EE_Click()
If chkCSR31EE = True Then Me.lblCSR31EE2.BackColor = &HFFFF&
If chkCSR31EE = False Then Me.lblCSR31EE2.BackColor = &HFFAFD7
If chkCSR31EE = True Then Me.chkCSR24X.Value = True
If chkCSR28BB = False And chkCSR29CC = False And chkCSR16P = False And chkCSR14N = False And chkCSR31EE = False And chkCSR32FF = False And chkCSR19S = False Then Me.chkCSR24X.Value = False
End Sub
Private Sub chkCSR32FF_Click()
If chkCSR32FF = True Then Me.lblCSR32FF2.BackColor = &HFFFF&
If chkCSR32FF = False Then Me.lblCSR32FF2.BackColor = &HFFAFD7
If chkCSR32FF = True Then Me.chkCSR24X.Value = True
If chkCSR28BB = False And chkCSR29CC = False And chkCSR16P = False And chkCSR14N = False And chkCSR31EE = False And chkCSR32FF = False And chkCSR19S = False Then Me.chkCSR24X.Value = False
End Sub
Private Sub chkCSR19S_Click()
If chkCSR19S = True Then Me.lblCSR19S2.BackColor = &HFFFF&
If chkCSR19S = False Then Me.lblCSR19S2.BackColor = &HFFAFD7
If chkCSR19S = True Then Me.chkCSR24X.Value = True
If chkCSR28BB = False And chkCSR29CC = False And chkCSR16P = False And chkCSR14N = False And chkCSR31EE = False And chkCSR32FF = False And chkCSR19S = False Then Me.chkCSR24X.Value = False
End Sub
' *************************** SUBGROUP Shipping
Private Sub chkCSR15O_Click()
If chkCSR15O = True Then Me.lblCSR15O2.BackColor = &HFFFF&
If chkCSR15O = False Then Me.lblCSR15O2.BackColor = &HC0FFC0
If chkCSR15O = True Then Me.chkCSR25Y.Value = True
If chkCSR15O = False And chkCSR17Q = False And chkCSR18R = False And chkCSR20T = False And chkCSR26ZZ = False And chkCSR30DD = False And chkCSR27AA = False Then Me.chkCSR25Y.Value = False
End Sub
Private Sub chkCSR18R_Click()
If chkCSR18R = True Then Me.lblCSR18R2.BackColor = &HFFFF&
If chkCSR18R = False Then Me.lblCSR18R2.BackColor = &HC0FFC0
If chkCSR18R = True Then Me.chkCSR25Y.Value = True
If chkCSR18R = False And chkCSR17Q = False And chkCSR15O = False And chkCSR20T = False And chkCSR26ZZ = False And chkCSR30DD = False And chkCSR27AA = False Then Me.chkCSR25Y.Value = False
End Sub
Private Sub chkCSR20T_Click()
If chkCSR20T = True Then Me.lblCSR20T2.BackColor = &HFFFF&
If chkCSR20T = False Then Me.lblCSR20T2.BackColor = &HC0FFC0
If chkCSR20T = True Then Me.chkCSR25Y.Value = True
If chkCSR20T = False And chkCSR17Q = False And chkCSR15O = False And chkCSR18R = False And chkCSR26ZZ = False And chkCSR30DD = False And chkCSR27AA = False Then Me.chkCSR25Y.Value = False
End Sub
Private Sub chkCSR26ZZ_Click() ' *****  26ZZ IS NEW TO THE MISC GROUP
If chkCSR26ZZ = True Then Me.lblCSR26ZZ2.BackColor = &HFFFF&
If chkCSR26ZZ = False Then Me.lblCSR26ZZ2.BackColor = &HC0FFC0
If chkCSR26ZZ = True Then Me.chkCSR25Y.Value = True
If chkCSR26ZZ = False And chkCSR17Q = False And chkCSR15O = False And chkCSR18R = False And chkCSR20T = False And chkCSR30DD = False And chkCSR27AA = False Then Me.chkCSR25Y.Value = False
End Sub
Private Sub chkCSR30DD_Click() ' *****  30DD IS NEW TO THE MISC GROUP
If chkCSR30DD = True Then Me.lblCSR30DD2.BackColor = &HFFFF&
If chkCSR30DD = False Then Me.lblCSR30DD2.BackColor = &HC0FFC0
If chkCSR30DD = True Then Me.chkCSR25Y.Value = True
If chkCSR30DD = False And chkCSR17Q = False And chkCSR15O = False And chkCSR18R = False And chkCSR20T = False And chkCSR26ZZ = False And chkCSR27AA = False Then Me.chkCSR25Y.Value = False
End Sub
Private Sub chkCSR17Q_Click()
If chkCSR17Q = True Then Me.lblCSR17Q2.BackColor = &HFFFF&
If chkCSR17Q = False Then Me.lblCSR17Q2.BackColor = &HC0FFC0
If chkCSR17Q = True Then Me.chkCSR25Y.Value = True
If chkCSR17Q = False And chkCSR30DD = False And chkCSR15O = False And chkCSR18R = False And chkCSR20T = False And chkCSR26ZZ = False And chkCSR27AA = False Then Me.chkCSR25Y.Value = False
End Sub
Private Sub chkCSR27AA_Click() ' *****  27AA IS NEW TO THE MISC GROUP
If chkCSR27AA = True Then Me.lblCSR27AA2.BackColor = &HFFFF&
If chkCSR27AA = False Then Me.lblCSR27AA2.BackColor = &HC0FFC0
If chkCSR27AA = True Then Me.chkCSR25Y.Value = True
If chkCSR27AA = False And chkCSR30DD = False And chkCSR15O = False And chkCSR18R = False And chkCSR20T = False And chkCSR26ZZ = False And chkCSR17Q = False Then Me.chkCSR25Y.Value = False
End Sub
' CSR CATAGORY BOXES (HIDDEN)
' *****************************************************************************************************
Private Sub chkCSR21U_Click()
     If chkCSR21U = True Then Me.lbl21U2.BackColor = &HFFFF&
     If chkCSR21U = False Then Me.lbl21U2.BackColor = &HFFC0C0
End Sub
Private Sub chkCSR22V_Click()
     If chkCSR22V = True Then Me.lbl22V2.BackColor = &HFFFF&
     If chkCSR22V = False Then Me.lbl22V2.BackColor = &HFFC0FF
End Sub
Private Sub chkCSR23W_Click()
     If chkCSR23W = True Then Me.lbl23W2.BackColor = &HFFFF&
     If chkCSR23W = False Then Me.lbl23W2.BackColor = &HFFFFC0
End Sub
Private Sub chkCSR24X_Click()
     If chkCSR24X = True Then Me.lbl24X2.BackColor = &HFFFF&
     If chkCSR24X = False Then Me.lbl24X2.BackColor = &HFFAFD7
End Sub
Private Sub chkCSR25Y_Click()
     If chkCSR25Y = True Then Me.lbl25Y2.BackColor = &HFFFF&
     If chkCSR25Y = False Then Me.lbl25Y2.BackColor = &HC0FFC0
End Sub
' SEATEX INTERNAL GROUP
' *****************************************************************************************************
Private Sub chkSEA_Click()
     If chkSEA = True Then Me.lblSEA0o.BackColor = &HFFFF&
     If chkSEA = False Then Me.lblSEA0o.BackColor = &HEFD5C3
End Sub
Private Sub chkSEA1A_Click()
     If chkSEA1A = True Then Me.lblSEA1A.ForeColor = &H0&
     If chkSEA1A = True Then Me.lblSEA1A2.BackColor = &HFFFF&
     If chkSEA1A = False Then Me.lblSEA1A.ForeColor = &HFFFFFF
     If chkSEA1A = False Then Me.lblSEA1A2.BackColor = &HC00000
End Sub
Private Sub chkSEA2B_Click()
     If chkSEA2B = True Then Me.lblSEA2B.ForeColor = &H0&
     If chkSEA2B = True Then Me.lblSEA2B2.BackColor = &HFFFF&
     If chkSEA2B = False Then Me.lblSEA2B.ForeColor = &HFFFFFF
     If chkSEA2B = False Then Me.lblSEA2B2.BackColor = &HC00000
End Sub
Private Sub chkSEA3C_Click()
     If chkSEA3C = True Then Me.lblSEA3C.ForeColor = &H0&
     If chkSEA3C = True Then Me.lblSEA3C2.BackColor = &HFFFF&
     If chkSEA3C = False Then Me.lblSEA3C.ForeColor = &HFFFFFF
     If chkSEA3C = False Then Me.lblSEA3C2.BackColor = &HC00000
End Sub
Private Sub chkSEA4D_Click()
     If chkSEA4D = True Then Me.lblSEA4D.ForeColor = &H0&
     If chkSEA4D = True Then Me.lblSEA4D2.BackColor = &HFFFF&
     If chkSEA4D = False Then Me.lblSEA4D.ForeColor = &HFFFFFF
     If chkSEA4D = False Then Me.lblSEA4D2.BackColor = &HC00000
End Sub
Private Sub chkSEA5E_Click()
     If chkSEA5E = True Then Me.lblSEA5E.ForeColor = &H0&
     If chkSEA5E = True Then Me.lblSEA5E2.BackColor = &HFFFF&
     If chkSEA5E = False Then Me.lblSEA5E.ForeColor = &HFFFFFF
     If chkSEA5E = False Then Me.lblSEA5E2.BackColor = &HC00000
End Sub
Private Sub chkSEA6F_Click()
     If chkSEA6F = True Then Me.lblSEA6F.ForeColor = &H0&
     If chkSEA6F = True Then Me.lblSEA6F2.BackColor = &HFFFF&
     If chkSEA6F = False Then Me.lblSEA6F.ForeColor = &HFFFFFF
     If chkSEA6F = False Then Me.lblSEA6F2.BackColor = &HC00000
End Sub
Private Sub chkSEA7G_Click()
     If chkSEA7G = True Then Me.lblSEA7G.ForeColor = &H0&
     If chkSEA7G = True Then Me.lblSEA7G2.BackColor = &HFFFF&
     If chkSEA7G = False Then Me.lblSEA7G.ForeColor = &HFFFFFF
     If chkSEA7G = False Then Me.lblSEA7G2.BackColor = &HC00000
End Sub
Private Sub chkSEA8H_Click()
     If chkSEA8H = True Then Me.lblSEA8H.ForeColor = &H0&
     If chkSEA8H = True Then Me.lblSEA8H2.BackColor = &HFFFF&
     If chkSEA8H = False Then Me.lblSEA8H.ForeColor = &HFFFFFF
     If chkSEA8H = False Then Me.lblSEA8H2.BackColor = &HC00000
End Sub
Private Sub chkSEA9I_Click()
     If chkSEA9I = True Then Me.lblSEA9I.ForeColor = &H0&
     If chkSEA9I = True Then Me.lblSEA9I2.BackColor = &HFFFF&
     If chkSEA9I = False Then Me.lblSEA9I.ForeColor = &HFFFFFF
     If chkSEA9I = False Then Me.lblSEA9I2.BackColor = &HC00000
End Sub
Private Sub chkSEA10J_Click()
     If chkSEA10J = True Then Me.lblSEA10J.ForeColor = &H0&
     If chkSEA10J = True Then Me.lblSEA10J2.BackColor = &HFFFF&
     If chkSEA10J = False Then Me.lblSEA10J.ForeColor = &HFFFFFF
     If chkSEA10J = False Then Me.lblSEA10J2.BackColor = &HC00000
End Sub
Private Sub chkSEA11K_Click()
     If chkSEA11K = True Then Me.lblSEA11K.ForeColor = &H0&
     If chkSEA11K = True Then Me.lblSEA11K2.BackColor = &HFFFF&
     If chkSEA11K = False Then Me.lblSEA11K.ForeColor = &HFFFFFF
     If chkSEA11K = False Then Me.lblSEA11K2.BackColor = &HC00000
End Sub
Private Sub chkSEA12L_Click()
     If chkSEA12L = True Then Me.lblSEA12L.ForeColor = &H0&
     If chkSEA12L = True Then Me.lblSEA12L2.BackColor = &HFFFF&
     If chkSEA12L = False Then Me.lblSEA12L.ForeColor = &HFFFFFF
     If chkSEA12L = False Then Me.lblSEA12L2.BackColor = &HC00000
End Sub
Private Sub chkSEA13M_Click()
     If chkSEA13M = True Then Me.lblSEA13M.ForeColor = &H0&
     If chkSEA13M = True Then Me.lblSEA13M2.BackColor = &HFFFF&
     If chkSEA13M = False Then Me.lblSEA13M.ForeColor = &HFFFFFF
     If chkSEA13M = False Then Me.lblSEA13M2.BackColor = &HC00000
End Sub
Private Sub chkSEA14N_Click()
     If chkSEA14N = True Then Me.lblSEA14N.ForeColor = &H0&
     If chkSEA14N = True Then Me.lblSEA14N2.BackColor = &HFFFF&
     If chkSEA14N = False Then Me.lblSEA14N.ForeColor = &HFFFFFF
     If chkSEA14N = False Then Me.lblSEA14N2.BackColor = &HC00000
End Sub
Private Sub chkSEA15O_Click()
     If chkSEA15O = True Then Me.lblSEA15O.ForeColor = &H0&
     If chkSEA15O = True Then Me.lblSEA15O2.BackColor = &HFFFF&
     If chkSEA15O = False Then Me.lblSEA15O.ForeColor = &HFFFFFF
     If chkSEA15O = False Then Me.lblSEA15O2.BackColor = &HC00000
End Sub
Private Sub chkSEA16P_Click()
     If chkSEA16P = True Then Me.lblSEA16P.ForeColor = &H0&
     If chkSEA16P = True Then Me.lblSEA16P2.BackColor = &HFFFF&
     If chkSEA16P = False Then Me.lblSEA16P.ForeColor = &HFFFFFF
     If chkSEA16P = False Then Me.lblSEA16P2.BackColor = &HC00000
End Sub
' MISCELANEOUS GROUP
' *****************************************************************************************************
Private Sub chkMIS_Click()
     If chkMIS = True Then Me.lblMIS0o.BackColor = &HFFFF&
     If chkMIS = True Then Me.lblMIS0o.ForeColor = &H0&
     If chkMIS = False Then Me.lblMIS0o.BackColor = &HEFD5C3
     If chkMIS = False Then Me.lblMIS0o.ForeColor = &H80000012
End Sub
Private Sub chkMIS1A_Click()
     If chkMIS1A = True Then Me.lblMIS1A2.BackColor = &HFFFF&
     If chkMIS1A = True Then Me.lblMIS1A.ForeColor = &H0&
     If chkMIS1A = False Then Me.lblMIS1A2.BackColor = &H800080
     If chkMIS1A = False Then Me.lblMIS1A.ForeColor = &HFFFFFF
End Sub
Private Sub chkMIS2B_Click()
     If chkMIS2B = True Then Me.lblMIS2B2.BackColor = &HFFFF&
     If chkMIS2B = True Then Me.lblMIS2B.ForeColor = &H0&
     If chkMIS2B = False Then Me.lblMIS2B2.BackColor = &H800080
     If chkMIS2B = False Then Me.lblMIS2B.ForeColor = &HFFFFFF
End Sub
Private Sub chkMIS3C_Click()
     If chkMIS3C = True Then Me.lblMIS3C2.BackColor = &HFFFF&
     If chkMIS3C = True Then Me.lblMIS3C.ForeColor = &H0&
     If chkMIS3C = False Then Me.lblMIS3C2.BackColor = &H800080
     If chkMIS3C = False Then Me.lblMIS3C.ForeColor = &HFFFFFF
End Sub
Private Sub chkMIS4D_Click()
     If chkMIS4D = True Then Me.lblMIS4D2.BackColor = &HFFFF&
     If chkMIS4D = True Then Me.lblMIS4D.ForeColor = &H0&
     If chkMIS4D = False Then Me.lblMIS4D2.BackColor = &H800080
     If chkMIS4D = False Then Me.lblMIS4D.ForeColor = &HFFFFFF
End Sub
Private Sub chkMIS5E_Click()
     If chkMIS5E = True Then Me.lblMIS5E2.BackColor = &HFFFF&
     If chkMIS5E = True Then Me.lblMIS5E.ForeColor = &H0&
     If chkMIS5E = False Then Me.lblMIS5E2.BackColor = &H800080
     If chkMIS5E = False Then Me.lblMIS5E.ForeColor = &HFFFFFF
End Sub
Private Sub chkMIS6F_Click()
     If chkMIS6F = True Then Me.lblMIS6F2.BackColor = &HFFFF&
     If chkMIS6F = True Then Me.lblMIS6F.ForeColor = &H0&
     If chkMIS6F = False Then Me.lblMIS6F2.BackColor = &H800080
     If chkMIS6F = False Then Me.lblMIS6F.ForeColor = &HFFFFFF
End Sub
Private Sub chkMIS7G_Click()
     If chkMIS7G = True Then Me.lblMIS7G2.BackColor = &HFFFF&
     If chkMIS7G = True Then Me.lblMIS7G.ForeColor = &H0&
     If chkMIS7G = False Then Me.lblMIS7G2.BackColor = &H800080
     If chkMIS7G = False Then Me.lblMIS7G.ForeColor = &HFFFFFF
End Sub
Private Sub chkMIS8H_Click()
     If chkMIS8H = True Then Me.lblMIS8H2.BackColor = &HFFFF&
     If chkMIS8H = True Then Me.lblMIS8H.ForeColor = &H0&
     If chkMIS8H = False Then Me.lblMIS8H2.BackColor = &H800080
     If chkMIS8H = False Then Me.lblMIS8H.ForeColor = &HFFFFFF
End Sub
Private Sub chkMIS9I_Click()
     If chkMIS9I = True Then Me.lblMIS9I2.BackColor = &HFFFF&
     If chkMIS9I = True Then Me.lblMIS9I.ForeColor = &H0&
     If chkMIS9I = False Then Me.lblMIS9I2.BackColor = &H800080
     If chkMIS9I = False Then Me.lblMIS9I.ForeColor = &HFFFFFF
End Sub
Private Sub chkMIS10J_Click()
     If chkMIS10J = True Then Me.lblMIS10J2.BackColor = &HFFFF&
     If chkMIS10J = True Then Me.lblMIS10J.ForeColor = &H0&
     If chkMIS10J = False Then Me.lblMIS10J2.BackColor = &H800080
     If chkMIS10J = False Then Me.lblMIS10J.ForeColor = &HFFFFFF
End Sub
' EHS GROUP
' *****************************************************************************************************
' &H000000FF& = RED
Private Sub chkEHS_Click()
     If chkEHS = True Then Me.lblEHS0o.BackColor = &HFFFF&
     If chkEHS = True Then Me.lblEHS0o.ForeColor = &H80000012
     If chkEHS = False Then Me.lblEHS0o.ForeColor = &H80000012
     If chkEHS = False Then Me.lblEHS0o.BackColor = &HFF&
End Sub
Private Sub chkEHS1A_Click()
     If chkEHS1A = True Then Me.lblEHS1A.BackColor = &HFFFF&
     If chkEHS1A = True Then Me.lblEHS1A2.BackColor = &HFFFF&
     If chkEHS1A = True Then Me.lblEHS1A.ForeColor = &H80000012
     If chkEHS1A = False Then Me.lblEHS1A.BackColor = &H404040
     If chkEHS1A = False Then Me.lblEHS1A2.BackColor = &H404040
     If chkEHS1A = False Then Me.lblEHS1A.ForeColor = &HFFFFFF
End Sub
Private Sub chkEHS2B_Click()
     If chkEHS2B = True Then Me.lblEHS2B.BackColor = &HFFFF&
     If chkEHS2B = True Then Me.lblEHS2B2.BackColor = &HFFFF&
     If chkEHS2B = True Then Me.lblEHS2B.ForeColor = &H80000012
     If chkEHS2B = False Then Me.lblEHS2B.BackColor = &H404040
     If chkEHS2B = False Then Me.lblEHS2B2.BackColor = &H404040
     If chkEHS2B = False Then Me.lblEHS2B.ForeColor = &HFFFFFF
End Sub
Private Sub chkEHS3C_Click()
     If chkEHS3C = True Then Me.lblEHS3C.BackColor = &HFFFF&
     If chkEHS3C = True Then Me.lblEHS3C2.BackColor = &HFFFF&
     If chkEHS3C = True Then Me.lblEHS3C.ForeColor = &H80000012
     If chkEHS3C = False Then Me.lblEHS3C.BackColor = &H404040
     If chkEHS3C = False Then Me.lblEHS3C2.BackColor = &H404040
     If chkEHS3C = False Then Me.lblEHS3C.ForeColor = &HFFFFFF
End Sub
Private Sub chkEHS4D_Click()
     If chkEHS4D = True Then Me.lblEHS4D.BackColor = &HFFFF&
     If chkEHS4D = True Then Me.lblEHS4D2.BackColor = &HFFFF&
     If chkEHS4D = True Then Me.lblEHS4D.ForeColor = &H80000012
     If chkEHS4D = False Then Me.lblEHS4D.BackColor = &H404040
     If chkEHS4D = False Then Me.lblEHS4D2.BackColor = &H404040
     If chkEHS4D = False Then Me.lblEHS4D.ForeColor = &HFFFFFF
End Sub
Private Sub chkEHS5E_Click()
     If chkEHS5E = True Then Me.lblEHS5E.BackColor = &HFFFF&
     If chkEHS5E = True Then Me.lblEHS5E2.BackColor = &HFFFF&
     If chkEHS5E = True Then Me.lblEHS5E.ForeColor = &H80000012
     If chkEHS5E = False Then Me.lblEHS5E.BackColor = &H404040
     If chkEHS5E = False Then Me.lblEHS5E2.BackColor = &H404040
     If chkEHS5E = False Then Me.lblEHS5E.ForeColor = &HFFFFFF
End Sub
Private Sub chkEHS6F_Click()
     If chkEHS6F = True Then Me.lblEHS6F.BackColor = &HFFFF&
     If chkEHS6F = True Then Me.lblEHS6F2.BackColor = &HFFFF&
     If chkEHS6F = True Then Me.lblEHS6F.ForeColor = &H80000012
     If chkEHS6F = False Then Me.lblEHS6F.BackColor = &H404040
     If chkEHS6F = False Then Me.lblEHS6F2.BackColor = &H404040
     If chkEHS6F = False Then Me.lblEHS6F.ForeColor = &HFFFFFF
End Sub
Private Sub chkEHS7G_Click()
     If chkEHS7G = True Then Me.lblEHS7G.BackColor = &HFFFF&
     If chkEHS7G = True Then Me.lblEHS7G2.BackColor = &HFFFF&
     If chkEHS7G = True Then Me.lblEHS7G.ForeColor = &H80000012
     If chkEHS7G = False Then Me.lblEHS7G.BackColor = &H404040
     If chkEHS7G = False Then Me.lblEHS7G2.BackColor = &H404040
     If chkEHS7G = False Then Me.lblEHS7G.ForeColor = &HFFFFFF
End Sub
Private Sub chkEHS8H_Click()
     If chkEHS8H = True Then Me.lblEHS8H.BackColor = &HFFFF&
     If chkEHS8H = True Then Me.lblEHS8H2.BackColor = &HFFFF&
     If chkEHS8H = True Then Me.lblEHS8H.ForeColor = &H80000012
     If chkEHS8H = False Then Me.lblEHS8H.BackColor = &H404040
     If chkEHS8H = False Then Me.lblEHS8H2.BackColor = &H404040
     If chkEHS8H = False Then Me.lblEHS8H.ForeColor = &HFFFFFF
End Sub
Private Sub chkEHS9I_Click()
     If chkEHS9I = True Then Me.lblEHS9I.BackColor = &HFFFF&
     If chkEHS9I = True Then Me.lblEHS9I2.BackColor = &HFFFF&
     If chkEHS9I = True Then Me.lblEHS9I.ForeColor = &H80000012
     If chkEHS9I = False Then Me.lblEHS9I.BackColor = &H404040
     If chkEHS9I = False Then Me.lblEHS9I2.BackColor = &H404040
     If chkEHS9I = False Then Me.lblEHS9I.ForeColor = &HFFFFFF
End Sub
Private Sub chkEHS10J_Click()
     If chkEHS10J = True Then Me.lblEHS10J.BackColor = &HFFFF&
     If chkEHS10J = True Then Me.lblEHS10J2.BackColor = &HFFFF&
     If chkEHS10J = True Then Me.lblEHS10J.ForeColor = &H80000012
     If chkEHS10J = False Then Me.lblEHS10J.BackColor = &H404040
     If chkEHS10J = False Then Me.lblEHS10J2.BackColor = &H404040
     If chkEHS10J = False Then Me.lblEHS10J.ForeColor = &HFFFFFF
End Sub
Private Sub chkEHS11K_Click()
     If chkEHS11K = True Then Me.lblEHS11K.BackColor = &HFFFF&
     If chkEHS11K = True Then Me.lblEHS11K2.BackColor = &HFFFF&
     If chkEHS11K = True Then Me.lblEHS11K.ForeColor = &H80000012
     If chkEHS11K = False Then Me.lblEHS11K.BackColor = &H404040
     If chkEHS11K = False Then Me.lblEHS11K2.BackColor = &H404040
     If chkEHS11K = False Then Me.lblEHS11K.ForeColor = &HFFFFFF
End Sub
Private Sub chkEHS12L_Click()
     If chkEHS12L = True Then Me.lblEHS12L.BackColor = &HFFFF&
     If chkEHS12L = True Then Me.lblEHS12L2.BackColor = &HFFFF&
     If chkEHS12L = True Then Me.lblEHS12L.ForeColor = &H80000012
     If chkEHS12L = False Then Me.lblEHS12L.BackColor = &H404040
     If chkEHS12L = False Then Me.lblEHS12L2.BackColor = &H404040
     If chkEHS12L = False Then Me.lblEHS12L.ForeColor = &HFFFFFF
End Sub
Private Sub chkAllIncidents_Click()
     If chkAllIncidents = True Then Me.lblAllIncidents2.BackColor = &HFFFF&
     If chkAllIncidents = True Then Me.lblAllIncidents.BackColor = &HFFFF&
     If chkAllIncidents = True Then Me.lblAllIncidents1.BackColor = &HFFFF&
     If chkAllIncidents = False Then Me.lblAllIncidents2.BackColor = &HC8DAC6
     If chkAllIncidents = False Then Me.lblAllIncidents.BackColor = &HC8DAC6
     If chkAllIncidents = False Then Me.lblAllIncidents1.BackColor = &HC8DAC6
End Sub
' **********************************************************************************************************************************************
' FORMATTING ALL OF THE PRESET DATE BUTTONS ON FORM
' **********************************************************************************************************************************************
Private Sub cmdAddDate2_Click()
ArrDateReport_FROM.Show
End Sub
Private Sub cmdAddDate3_Click()
ArrDateReport_TO.Show
End Sub
Private Sub cmdTEST_Click()
Me.txtDateBox2.Value = "5/21/2019"
Me.txtDateBox3.Value = "5/25/2019"
End Sub
'*********************************************************************************
Private Sub cmd2015_Click() '2021 (ytd)  #1
Me.txtDateBox2.Value = "1/1/2021"
Me.txtDateBox3.Value = Now()
cmd2015.BackColor = &HFFFF&
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmd2016_Click() ' 2020 (all)  #2
Me.txtDateBox2.Value = "1/1/2020"
Me.txtDateBox3.Value = "12/31/2020"
cmd2016.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmd2017_Click() ' 2019 (all)  #3
Me.txtDateBox2.Value = "1/1/2019"
Me.txtDateBox3.Value = "12/31/2019"
cmd2017.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmd2018_Click() ' 2018 (all)  #4
Me.txtDateBox2.Value = "1/1/2018"
Me.txtDateBox3.Value = "12/31/2018"
cmd2018.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmd2019_Click() ' 2017 (all)  #5
Me.txtDateBox2.Value = "1/1/2017"
Me.txtDateBox3.Value = "12/31/2017"
cmd2019.BackColor = &HFFFF&    ' &HFFFF&
cmd2015.BackColor = &HC8DAC6   ' &HC8DAC6
cmd2016.BackColor = &HC8DAC6   ' &HC8DAC6
cmd2017.BackColor = &HC8DAC6   ' &HC8DAC6
cmd2018.BackColor = &HC8DAC6   ' &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdAug2019_Click() ' April 2021  #6
Me.txtDateBox2.Value = "4/1/2021"
Me.txtDateBox3.Value = Now()
cmdAug2019.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdJuly2019_Click() ' March 2021  #7
Me.txtDateBox2.Value = "3/1/2021"
Me.txtDateBox3.Value = "3/31/2021"
cmdJuly2019.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdJune2019_Click() ' Feb 2021  #8
Me.txtDateBox2.Value = "2/1/2021"
Me.txtDateBox3.Value = "2/28/2021"
cmdJune2019.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdMay2019_Click() ' Jan 2021  #9
Me.txtDateBox2.Value = "1/1/2021"
Me.txtDateBox3.Value = "1/31/2021"
cmdJune2019.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdApril2019_Click() ' Dec 2020  #10
Me.txtDateBox2.Value = "12/1/2020"
Me.txtDateBox3.Value = "12/31/2020"
cmdApril2019.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdMarch2019_Click() ' Nov 2020  #11
Me.txtDateBox2.Value = "11/1/2020"
Me.txtDateBox3.Value = "11/30/2019"
cmdMarch2019.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdFeb2019_Click() ' Oct 2020  #12
Me.txtDateBox2.Value = "10/1/2020"
Me.txtDateBox3.Value = "10/31/2020"
cmdFeb2019.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdJan2019_Click() ' Sept 2020  #13
Me.txtDateBox2.Value = "9/1/2020"
Me.txtDateBox3.Value = "9/30/2020"
cmdJan2019.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdDec2018_Click() ' Aug 2020  #14
Me.txtDateBox2.Value = "8/1/2020"
Me.txtDateBox3.Value = "8/29/2020"
cmdDec2018.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdNov2018_Click() ' July 2020  #15
Me.txtDateBox2.Value = "8/1/2020"
Me.txtDateBox3.Value = "8/31/2020"
cmdNov2018.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdOct2018_Click() ' June 2020  #16
Me.txtDateBox2.Value = "6/1/2020"
Me.txtDateBox3.Value = "6/30/2020"
cmdOct2018.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdSept2018_Click() ' May 2020  #17
Me.txtDateBox2.Value = "5/1/2020"
Me.txtDateBox3.Value = "5/30/2020"
cmdSept2018.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdAug2018_Click() ' April 2020  #18
Me.txtDateBox2.Value = "5/1/2020"
Me.txtDateBox3.Value = "5/30/2020"
cmdAug2018.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdJuly2018_Click() ' March 2020  #19
Me.txtDateBox2.Value = "3/1/2020"
Me.txtDateBox3.Value = "3/11/2020"
cmdJuly2018.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJune2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
Private Sub cmdJune2018_Click() ' Feb 2020  #20
Me.txtDateBox2.Value = "2/1/2020"
Me.txtDateBox3.Value = "2/29/2020"
cmdJune2018.BackColor = &HFFFF&
cmd2015.BackColor = &HC8DAC6
cmd2016.BackColor = &HC8DAC6
cmd2017.BackColor = &HC8DAC6
cmd2018.BackColor = &HC8DAC6
cmd2019.BackColor = &HC8DAC6
cmdJan2019.BackColor = &HC8DAC6
cmdFeb2019.BackColor = &HC8DAC6
cmdMarch2019.BackColor = &HC8DAC6
cmdApril2019.BackColor = &HC8DAC6
cmdAug2019.BackColor = &HC8DAC6
cmdJune2019.BackColor = &HC8DAC6
cmdJuly2019.BackColor = &HC8DAC6
'cmdAug2019.BackColor = &HC8DAC6
'cmdMay2018.BackColor = &HC8DAC6
cmdJuly2018.BackColor = &HC8DAC6
cmdAug2018.BackColor = &HC8DAC6
cmdSept2018.BackColor = &HC8DAC6
cmdOct2018.BackColor = &HC8DAC6
cmdNov2018.BackColor = &HC8DAC6
cmdDec2018.BackColor = &HC8DAC6
End Sub
'*********************************************************************************
'*********************************************************************************
'*********************************************************************************
'*********************************************************************************
'*********************************************************************************
'**********************************************************************************************************************************************
' CODE FOR THE RUNNING ALL THE MACROS AND EXECUTIONS

Private Sub UserForm_Initialize()
' **********************************************************************************************************************************************
' PREDETERMINED DATES FOR THE PRESET BUTTONS FOR MONTHS AND YEARS
    With txtDateBox2
        .Text = "mm/dd/yyyy"
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    With txtDateBox3
        .Text = "mm/dd/yyyy"
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
End Sub
' **********************************************************************************************************************************************
' CODE FOR SELECTING JUST THE RECORDS ON THE WORKSHEET "SEATEX INCIDENT LOG" THAT FALL WITHIN THE DATE RANGE THAT WAS PREVISOULY SELECTED ON THE USERFORM

Private Sub cmdGetReport_Click()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
' *************************
Dim aall As String
Dim CSR0o As String
Dim CSR1A As String
Dim CSR2B As String
Dim CSR3C As String
Dim CSR4D As String
Dim CSR5E As String
Dim CSR6F As String
Dim CSR7G As String
Dim CSR8H As String
Dim CSR9I As String
Dim CSR10J As String
Dim CSR11K As String
Dim CSR12L As String
Dim CSR13M As String
Dim CSR14N As String
Dim CSR15O As String
Dim CSR16P As String
Dim CSR17Q As String
Dim CSR18R As String
Dim CSR19S As String
Dim CSR20T As String
Dim CSR21U As String
Dim CSR22V As String
Dim CSR23W As String
Dim CSR24X As String
Dim CSR25Y As String
Dim CSR26ZZ As String
Dim CSR27AA As String
Dim CSR28BB As String
Dim CSR29CC As String
Dim CSR30DD As String
Dim CSR31EE As String
Dim CSR32FF As String
Dim SEA0o As String
Dim SEA1A As String
Dim SEA2B As String
Dim SEA3C As String
Dim SEA4D As String
Dim SEA5E As String
Dim SEA6F As String
Dim SEA7G As String
Dim SEA8H As String
Dim SEA9I As String
Dim SEA10J As String
Dim SEA11K As String
Dim SEA12L As String
Dim SEA13M As String
Dim SEA14N As String
Dim SEA15O As String
Dim SEA16P As String
Dim MIS0o As String
Dim MIS1A As String
Dim MIS2B As String
Dim MIS3C As String
Dim MIS4D As String
Dim MIS5E As String
Dim MIS6F As String
Dim MIS7G As String
Dim MIS8H As String
Dim MIS9I As String
Dim MIS10J As String
Dim EHS0o As String
Dim EHS1A As String
Dim EHS2B As String
Dim EHS3C As String
Dim EHS4D As String
Dim EHS5E As String
Dim EHS6F As String
Dim EHS7G As String
Dim EHS8H As String
Dim EHS9I As String
Dim EHS10J As String
Dim EHS11K As String
Dim EHS12L As String
'***************************
Dim CSR0oCNT As Long
Dim CSR1ACNT As Long
Dim CSR2BCNT As Long
Dim CSR3CCNT As Long
Dim CSR4DCNT As Long
Dim CSR5ECNT As Long
Dim CSR6FCNT As Long
Dim CSR7GCNT As Long
Dim CSR8HCNT As Long
Dim CSR9ICNT As Long
Dim CSR10JCNT As Long
Dim CSR11KCNT As Long
Dim CSR12LCNT As Long
Dim CSR13MCNT As Long
Dim CSR14NCNT As Long
Dim CSR15OCNT As Long
Dim CSR16PCNT As Long
Dim CSR17QCNT As Long
Dim CSR18RCNT As Long
Dim CSR19SCNT As Long
Dim CSR20TCNT As Long
Dim CSR21UCNT As Long
Dim CSR22VCNT As Long
Dim CSR23WCNT As Long
Dim CSR24XCNT As Long
Dim CSR25YCNT As Long
Dim CSR26ZZCNT As Long
Dim CSR27AACNT As Long
Dim CSR28BBCNT As Long
Dim CSR29CCCNT As Long
Dim CSR30DDCNT As Long
Dim CSR31EECNT As Long
Dim CSR32FFCNT As Long
Dim SEA0oCNT As Long
Dim SEA1ACNT As Long
Dim SEA2BCNT As Long
Dim SEA3CCNT As Long
Dim SEA4DCNT As Long
Dim SEA5ECNT As Long
Dim SEA6FCNT As Long
Dim SEA7GCNT As Long
Dim SEA8HCNT As Long
Dim SEA9ICNT As Long
Dim SEA10JCNT As Long
Dim SEA11KCNT As Long
Dim SEA12LCNT As Long
Dim SEA13MCNT As Long
Dim SEA14NCNT As Long
Dim SEA15OCNT As Long
Dim SEA16PCNT As Long
Dim MIS0oCNT As Long
Dim MIS1ACNT As Long
Dim MIS2BCNT As Long
Dim MIS3CCNT As Long
Dim MIS4DCNT As Long
Dim MIS5ECNT As Long
Dim MIS6FCNT As Long
Dim MIS7GCNT As Long
Dim MIS8HCNT As Long
Dim MIS9ICNT As Long
Dim MIS10JCNT As Long
Dim EHS0oCNT As Long
Dim EHS1ACNT As Long
Dim EHS2BCNT As Long
Dim EHS3CCNT As Long
Dim EHS4DCNT As Long
Dim EHS5ECNT As Long
Dim EHS6FCNT As Long
Dim EHS7GCNT As Long
Dim EHS8HCNT As Long
Dim EHS9ICNT As Long
Dim EHS10JCNT As Long
Dim EHS11KCNT As Long
Dim EHS12LCNT As Long
'***************************
CSR0o = 0
CSR1A = 0
CSR2B = 0
CSR3C = 0
CSR4D = 0
CSR5E = 0
CSR6F = 0
CSR7G = 0
CSR8H = 0
CSR9I = 0
CSR10J = 0
CSR11K = 0
CSR12L = 0
CSR13M = 0
CSR14N = 0
CSR15O = 0
CSR16P = 0
CSR17Q = 0
CSR18R = 0
CSR19S = 0
CSR20T = 0
CSR21U = 0
CSR22V = 0
CSR23W = 0
CSR24X = 0
CSR25Y = 0
CSR26ZZ = 0
CSR27AA = 0
CSR28BB = 0
CSR29CC = 0
CSR30DD = 0
CSR31EE = 0
CSR32FF = 0
SEA0o = 0
SEA1A = 0
SEA2B = 0
SEA3C = 0
SEA4D = 0
SEA5E = 0
SEA6F = 0
SEA7G = 0
SEA8H = 0
SEA9I = 0
SEA10J = 0
SEA11K = 0
SEA12L = 0
SEA13M = 0
SEA14N = 0
SEA15O = 0
SEA16P = 0
MIS0o = 0
MIS1A = 0
MIS2B = 0
MIS3C = 0
MIS4D = 0
MIS5E = 0
MIS6F = 0
MIS7G = 0
MIS8H = 0
MIS9I = 0
MIS10J = 0
EHS0o = 0
EHS1A = 0
EHS2B = 0
EHS3C = 0
EHS4D = 0
EHS5E = 0
EHS6F = 0
EHS7G = 0
EHS8H = 0
EHS9I = 0
EHS10J = 0
EHS11K = 0
EHS12L = 0
'***************************
Dim cP As Range
Dim dP As Range
Dim aRow As Long
Dim dRow As Long
Dim bRow As Long
Dim tCnt As Long
Dim qRow As Long
Dim ws As Worksheet
Dim yz As Worksheet
Dim cr As Worksheet
Dim lCol As Long
Dim rCol As Long
Dim xRow As Long
Dim yRow As Long
Dim rRow As Long
Dim sRow As Long
Dim iCell As Variant
Dim W1Startdate As String, W1Enddate As String
Dim Cell As Range
Dim eCol As Long
'***************************
'set object variable to worksheet
    eCol = ActiveWorkbook.Worksheets("REPORTS").UsedRange.Rows.Count
    rCol = Sheets("Seatex Incident Log").UsedRange.Rows.Count
    Set ws = ThisWorkbook.Worksheets("Seatex Incident Log")
    Set yz = ThisWorkbook.Worksheets("REPORTS")
    Set cr = ThisWorkbook.Worksheets("ANALYTICS")
'**********************************************************************************************************************************************
' UN-HIDE "ANALYTICS" WORKSHEET AND CLEAR ALL THE PREVIOUSLY POSTED ANALYTICAL DATA FROM THE LAST SEARCH:
ActiveWorkbook.Worksheets("ANALYTICS").Activate
ActiveWorkbook.Worksheets("ANALYTICS").Visible = True
ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 5), Cells(77, 16)).Select
ActiveWorkbook.Worksheets("ANALYTICS").Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

ActiveWorkbook.Worksheets("REPORTS").Activate
ActiveWorkbook.Worksheets("REPORTS").Visible = True
ActiveWorkbook.Worksheets("REPORTS").Range(Cells(18, 1), Cells(eCol, 1)).Select
ActiveWorkbook.Worksheets("REPORTS").Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

ActiveWorkbook.Worksheets("REPORTS").Range(Cells(2, 1), Cells(15, 10)).Select
ActiveWorkbook.Worksheets("REPORTS").Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

    If txtDateBox3.Value < txtDateBox2 Then
    
    MsgBox "You have entered an incorrect date value.  Please enter a real date"
        With txtDateBox2
        .Text = "mm/dd/yyyy"
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    With txtDateBox3
        .Text = "mm/dd/yyyy"
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    Exit Sub
    End If
'**********************************************************************************************************************************************
' UN-HIDE "REPORTS" WORKSHEET AND CLEAR ANY REMAINING DATE FROM THE PREVIOUS SORT OR ACTIVITY
ActiveWorkbook.Worksheets("REPORTS").Visible = True
ActiveWorkbook.Worksheets("REPORTS").Activate
'**********************************************************************************************************************************************
' UNHIDE THE ROWS THAT PREVIOUSLY HIDDEN FROM ANY PREVIOUS EVENTS
ActiveWorkbook.Worksheets("REPORTS").Range("A18").EntireRow.Select
ActiveWorkbook.Worksheets("REPORTS").Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Hidden = False
'**********************************************************************************************************************************************
' CLEAR ALL DATA FROM ROW #18 & DOWN TO LAST ROW
ActiveWorkbook.Worksheets("REPORTS").Range("A18").EntireRow.Select
ActiveWorkbook.Worksheets("REPORTS").Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
'**********************************************************************************************************************************************
' ASSIGN THE VALUE OF THE DATE BOXES TO VARIABLES
W1Startdate = Me.txtDateBox2.Value      ' "FROM" date
W1Enddate = Me.txtDateBox3.Value        ' "TO" date
'**********************************************************************************************************************************************
' STEP (1): SORT ROWS THAT ONLY FALL INBETWEEN THE 2 DATES THAT WERE SELECTED IN THE DATEBOXES
    'On Error GoTo ErrorMask
    
     With ws
        ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
            .Sort.SortFields.Clear

        With .Range(Cells(17, 1), Cells(rCol, 49))

'clear filter
        .AutoFilter
        .AutoFilter Field:=2, _
                    Criteria1:=">=" & CLng(DateValue(W1Startdate)), _
                    Criteria2:="<=" & CLng(DateValue(W1Enddate))
        End With
    End With

' STEP (2): COPY THE DATE-ONLY FILTERED ROWS
'**********************************************************************************************************************************************
ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
ActiveWorkbook.Worksheets("Seatex Incident Log").Range(Cells(18, 1), Cells(rCol, 49)).SpecialCells(xlCellTypeVisible).Select
Selection.Copy
'**********************************************************************************************************************************************
' STEP (3): PASTE THE PREVISOULY COPIED ROWS IN STEP 2 ONTO THE WORKSHEET "REPORTS"
ActiveWorkbook.Worksheets("REPORTS").Activate
ActiveWorkbook.Worksheets("REPORTS").Range("B18").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
        Application.CutCopyMode = False
'**********************************************************************************************************************************************
' HIDE THE ROWS THAT WERE COPIED SO THAT THE ROWS THAT MATCH THE CHECKBOX VALUE CAN BE UN-HIDDEN

Range(Cells(18, 1), Cells(rCol, 26)).EntireRow.Hidden = True

' STEP (4): CODE FOR FILTERING DOWN THE ROWS TO ONLY INCLUDE THOSE WITH THE CODE THAT ALIGNS WITH WHATEVER CHECKBOXES WERE CHECKED ON THE FORM:
'Dim Cell As Range

rRow = ActiveWorkbook.Worksheets("REPORTS").UsedRange.Rows.Count

For Each Cell In Range(Cells(18, 16), Cells(rRow, 16))
'**********************************************************************************************************************************************
If Cell Like "*ALL*" And chkAllIncidents.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR0o*" And chkCSR.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR1A*" And chkCSR1A.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR2B*" And chkCSR2B.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR3C*" And chkCSR3C.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR4D*" And chkCSR4D.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR5E*" And chkCSR5E.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR6F*" And chkCSR6F.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR7G*" And chkCSR7G.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR8H*" And chkCSR8H.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR9I*" And chkCSR9I.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR10J*" And chkCSR10J.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR11K*" And chkCSR11K.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR12L*" And chkCSR12L.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR13M*" And chkCSR13M.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR14N*" And chkCSR14N.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR15O*" And chkCSR15O.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR16P*" And chkCSR16P.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR17Q*" And chkCSR17Q.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR18R*" And chkCSR18R.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR19S*" And chkCSR19S.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR20T*" And chkCSR20T.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR21U*" And chkCSR21U.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR22V*" And chkCSR22V.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR23W*" And chkCSR23W.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR24X*" And chkCSR24X.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR25Y*" And chkCSR25Y.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR26ZZ*" And chkCSR26ZZ.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR27AA*" And chkCSR27AA.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR28BB*" And chkCSR28BB.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR29CC*" And chkCSR29CC.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR30DD*" And chkCSR30DD.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR31EE*" And chkCSR31EE.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*CSR32FF*" And chkCSR32FF.Value = True Then Rows(Cell.Row).Hidden = False
' ******************************************************************************************
If Cell Like "*SEA1A*" And chkSEA1A.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA2B*" And chkSEA2B.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA3C*" And chkSEA3C.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA4D*" And chkSEA4D.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA5E*" And chkSEA5E.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA6F*" And chkSEA6F.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA7G*" And chkSEA7G.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA8H*" And chkSEA8H.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA9I*" And chkSEA9I.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA10J*" And chkSEA10J.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA11K*" And chkSEA11K.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA12L*" And chkSEA12L.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA13M*" And chkSEA13M.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA14N*" And chkSEA14N.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA15O*" And chkSEA15O.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*SEA16P*" And chkSEA16P.Value = True Then Rows(Cell.Row).Hidden = False
' ******************************************************************************************
If Cell Like "*MIS1A*" And chkMIS1A.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*MIS2B*" And chkMIS2B.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*MIS3C*" And chkMIS3C.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*MIS4D*" And chkMIS4D.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*MIS5E*" And chkMIS5E.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*MIS6F*" And chkMIS6F.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*MIS7G*" And chkMIS7G.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*MIS8H*" And chkMIS8H.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*MIS9I*" And chkMIS9I.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*MIS10J*" And chkMIS10J.Value = True Then Rows(Cell.Row).Hidden = False
' ******************************************************************************************
If Cell Like "*EHS1A*" And chkEHS1A.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*EHS2B*" And chkEHS2B.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*EHS3C*" And chkEHS3C.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*EHS4D*" And chkEHS4D.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*EHS5E*" And chkEHS5E.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*EHS6F*" And chkEHS6F.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*EHS7G*" And chkEHS7G.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*EHS8H*" And chkEHS8H.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*EHS9I*" And chkEHS9I.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*EHS10J*" And chkEHS10J.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*EHS11K*" And chkEHS11K.Value = True Then Rows(Cell.Row).Hidden = False
If Cell Like "*EHS12L*" And chkEHS12L.Value = True Then Rows(Cell.Row).Hidden = False
    
 Next
'**********************************************************************************************************************************************
 xRow = ActiveWorkbook.Worksheets("REPORTS").UsedRange.Rows.Count
On Error GoTo bm_Error_norecords
For Each Cell In Range(Cells(18, 16), Cells(xRow, 16)).SpecialCells(xlCellTypeVisible)
On Error GoTo bm_Error_norecords
'**********************************************************************************************************************************************
If Cell Like "*CSR1A*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "hazardous label issues " & vbCrLf
If Cell Like "*CSR2B*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "wrong or incorrect labels " & vbCrLf
If Cell Like "*CSR3C*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "missing labels " & vbCrLf
If Cell Like "*CSR4D*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "label placement issues " & vbCrLf
If Cell Like "*CSR5E*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "lids or caps issues " & vbCrLf
If Cell Like "*CSR6F*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "incorrect packaging " & vbCrLf
If Cell Like "*CSR7G*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "packaging failure issues " & vbCrLf
If Cell Like "*CSR8H*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "mixed products in package " & vbCrLf
If Cell Like "*CSR9I*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "leaking product incidents " & vbCrLf
If Cell Like "*CSR10J*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "off or wrong color " & vbCrLf
If Cell Like "*CSR11K*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "performance issues " & vbCrLf
If Cell Like "*CSR12L*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "contamination issues " & vbCrLf
If Cell Like "*CSR13M*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "product doesnt look the same " & vbCrLf
If Cell Like "*CSR14N*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "billing paperwork issues " & vbCrLf
If Cell Like "*CSR15O*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "missed shipment or arrived late " & vbCrLf
If Cell Like "*CSR16P*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "sloppy workmanship issues " & vbCrLf
If Cell Like "*CSR17Q*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "carrier/shipper issues " & vbCrLf
If Cell Like "*CSR18R*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "received incorrect quantity " & vbCrLf
If Cell Like "*CSR19S*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "material still left over in tanker " & vbCrLf
If Cell Like "*CSR20T*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "received wrong product incidents " & vbCrLf
If Cell Like "*CSR26ZZ*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "customer received damaged products issue" & vbCrLf
If Cell Like "*CSR27AA*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "customer received wrong product incidents" & vbCrLf
If Cell Like "*CSR28BB*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "order entry or chempax issues" & vbCrLf
If Cell Like "*CSR29CC*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "scheduling issues incidents" & vbCrLf
If Cell Like "*CSR30DD*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "product shipped w/ wrong paperwork issues" & vbCrLf
If Cell Like "*CSR31EE*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "raw material issues (COP incidents only)" & vbCrLf
If Cell Like "*CSR32FF*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "issues regarding inventory related incidents" & vbCrLf
'******************************************************************************************
If Cell Like "*SEA1A*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "all process failures " & vbCrLf
If Cell Like "*SEA2B*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "all process deviations " & vbCrLf
If Cell Like "*SEA3C*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "all contamination incidents " & vbCrLf
If Cell Like "*SEA4D*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "mechanical or equip failures " & vbCrLf
If Cell Like "*SEA5E*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "document paperwork incidents " & vbCrLf
If Cell Like "*SEA6F*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "raw material issues (just material or products) & vbCrLf"
If Cell Like "*SEA7G*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "raw material issues (NOT material or products) & vbCrLf"
If Cell Like "*SEA8H*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "customer owned material issues " & vbCrLf
If Cell Like "*SEA9I*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "customer owned(not product) " & vbCrLf
If Cell Like "*SEA10J*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "leaking hose pipe fitting incidents " & vbCrLf
If Cell Like "*SEA11K*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "workmanship issues " & vbCrLf
If Cell Like "*SEA12L*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "inventory incidents " & vbCrLf
If Cell Like "*SEA13M*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "carrier/shipper incidents " & vbCrLf
If Cell Like "*SEA14N*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "other (not listed) " & vbCrLf
If Cell Like "*SEA15O*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "hazardous labels or placard issues " & vbCrLf
If Cell Like "*SEA16P*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "packaging related issues " & vbCrLf
'******************************************************************************************
If Cell Like "*MIS1A*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "supplier related incidents " & vbCrLf
If Cell Like "*MIS2B*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "contractor issues " & vbCrLf
If Cell Like "*MIS3C*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "audit related finding " & vbCrLf
If Cell Like "*MIS4D*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "theft of property " & vbCrLf
If Cell Like "*MIS5E*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "unexpected alarm " & vbCrLf
If Cell Like "*MIS6F*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "railcar or railcar area incident " & vbCrLf
If Cell Like "*MIS7G*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "MDR rosenberg " & vbCrLf
If Cell Like "*MIS8H*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "MDR el campo " & vbCrLf
If Cell Like "*MIS9I*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "personnel issues conflict " & vbCrLf
If Cell Like "*MIS10J*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "other not listed miscellaneous " & vbCrLf
'******************************************************************************************
If Cell Like "*EHS1A*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS injury " & vbCrLf
If Cell Like "*EHS2B*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS near miss " & vbCrLf
If Cell Like "*EHS3C*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS slip trip fall " & vbCrLf
If Cell Like "*EHS4D*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS struck by object " & vbCrLf
If Cell Like "*EHS5E*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS exposure " & vbCrLf
If Cell Like "*EHS6F*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS splashed w chemical " & vbCrLf
If Cell Like "*EHS7G*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS respirator related " & vbCrLf
If Cell Like "*EHS8H*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS mobile(fork truck) traffic " & vbCrLf
If Cell Like "*EHS9I*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS gas excursion " & vbCrLf
If Cell Like "*EHS10J*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS other not listed " & vbCrLf
If Cell Like "*EHS11K*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS spills spill related incidents " & vbCrLf
If Cell Like "*EHS12L*" Then Cells(Cell.Row, "A").Value = Cells(Cell.Row, "A") & "EHS PPE related " & vbCrLf
'******************************************************************************************

Next
   
' STEP (6): FORMAT COLUMN "A" WHICH CONTAIN THE DESCRIPTION CATAGORY FOR THE TYPE OF INCIDENT FOR EACH INCIDENT
' STEP (6.5): POST THE NUMBER OF INCIDENTS SHOWING ON THE WORKSHEET
' **********************************************************************************************************************************************
' *************************

    qRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    bRow = ActiveSheet.UsedRange.Cells.Find("*", , , , xlByRows, xlPrevious).Row
    tCnt = ActiveSheet.Range(Cells(18, 2), Cells(bRow, 2)).SpecialCells(xlCellTypeVisible).Cells.Count

aRow = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Row

    For Each cP In Range(Cells(18, 16), Cells(aRow, 16)).SpecialCells(xlCellTypeVisible)
If cP Like "*ALL*" Then ALL = ALL + 1
If cP Like "*CSR0o*" Then CSR0o = CSR0o + 1
If cP Like "*CSR1A*" Then CSR1A = CSR1A + 1
If cP Like "*CSR2B*" Then CSR2B = CSR2B + 1
If cP Like "*CSR3C*" Then CSR3C = CSR3C + 1
If cP Like "*CSR4D*" Then CSR4D = CSR4D + 1
If cP Like "*CSR5E*" Then CSR5E = CSR5E + 1
If cP Like "*CSR6F*" Then CSR6F = CSR6F + 1
If cP Like "*CSR7G*" Then CSR7G = CSR7G + 1
If cP Like "*CSR8H*" Then CSR8H = CSR8H + 1
If cP Like "*CSR9I*" Then CSR9I = CSR9I + 1
If cP Like "*CSR10J*" Then CSR10J = CSR10J + 1
If cP Like "*CSR11K*" Then CSR11K = CSR11K + 1
If cP Like "*CSR12L*" Then CSR12L = CSR12L + 1
If cP Like "*CSR13M*" Then CSR13M = CSR13M + 1
If cP Like "*CSR14N*" Then CSR14N = CSR14N + 1
If cP Like "*CSR15O*" Then CSR15O = CSR15O + 1
If cP Like "*CSR16P*" Then CSR16P = CSR16P + 1
If cP Like "*CSR17Q*" Then CSR17Q = CSR17Q + 1
If cP Like "*CSR18R*" Then CSR18R = CSR18R + 1
If cP Like "*CSR19S*" Then CSR19S = CSR19S + 1
If cP Like "*CSR20T*" Then CSR20T = CSR20T + 1
If cP Like "*CSR21U*" Then CSR21U = CSR21U + 1
If cP Like "*CSR22V*" Then CSR22V = CSR22V + 1
If cP Like "*CSR23W*" Then CSR23W = CSR23W + 1
If cP Like "*CSR24X*" Then CSR24X = CSR24X + 1
If cP Like "*CSR25Y*" Then CSR25Y = CSR25Y + 1
If cP Like "*CSR26ZZ*" Then CSR26ZZ = CSR26ZZ + 1
If cP Like "*CSR27AA*" Then CSR27AA = CSR27AA + 1
If cP Like "*CSR28BB*" Then CSR28BB = CSR28BB + 1
If cP Like "*CSR29CC*" Then CSR29CC = CSR29CC + 1
If cP Like "*CSR30DD*" Then CSR30DD = CSR30DD + 1
If cP Like "*CSR31EE*" Then CSR31EE = CSR31EE + 1
If cP Like "*CSR32FF*" Then CSR32FF = CSR32FF + 1
'******************************************************************************************
If cP Like "*SEA0o*" Then SEA0o = SEA0o + 1
If cP Like "*SEA1A*" Then SEA1A = SEA1A + 1
If cP Like "*SEA2B*" Then SEA2B = SEA2B + 1
If cP Like "*SEA3C*" Then SEA3C = SEA3C + 1
If cP Like "*SEA4D*" Then SEA4D = SEA4D + 1
If cP Like "*SEA5E*" Then SEA5E = SEA5E + 1
If cP Like "*SEA6F*" Then SEA6F = SEA6F + 1
If cP Like "*SEA7G*" Then SEA7G = SEA7G + 1
If cP Like "*SEA8H*" Then SEA8H = SEA8H + 1
If cP Like "*SEA9I*" Then SEA9I = SEA9I + 1
If cP Like "*SEA10J*" Then SEA10J = SEA10J + 1
If cP Like "*SEA11K*" Then SEA11K = SEA11K + 1
If cP Like "*SEA12L*" Then SEA12L = SEA12L + 1
If cP Like "*SEA13M*" Then SEA13M = SEA13M + 1
If cP Like "*SEA14N*" Then SEA14N = SEA14N + 1
If cP Like "*SEA15O*" Then SEA15O = SEA15O + 1
If cP Like "*SEA16P*" Then SEA16P = SEA16P + 1
'******************************************************************************************
If cP Like "*MIS0o*" Then MIS0o = MIS0o + 1
If cP Like "*MIS1A*" Then MIS1A = MIS1A + 1
If cP Like "*MIS2B*" Then MIS2B = MIS2B + 1
If cP Like "*MIS3C*" Then MIS3C = MIS3C + 1
If cP Like "*MIS4D*" Then MIS4D = MIS4D + 1
If cP Like "*MIS5E*" Then MIS5E = MIS5E + 1
If cP Like "*MIS6F*" Then MIS6F = MIS6F + 1
If cP Like "*MIS7G*" Then MIS7G = MIS7G + 1
If cP Like "*MIS8H*" Then MIS8H = MIS8H + 1
If cP Like "*MIS9I*" Then MIS9I = MIS9I + 1
If cP Like "*MIS10J*" Then MIS10J = MIS10J + 1
'******************************************************************************************
If cP Like "*EHS0o*" Then EHS0o = EHS0o + 1
If cP Like "*EHS1A*" Then EHS1A = EHS1A + 1
If cP Like "*EHS2B*" Then EHS2B = EHS2B + 1
If cP Like "*EHS3C*" Then EHS3C = EHS3C + 1
If cP Like "*EHS4D*" Then EHS4D = EHS4D + 1
If cP Like "*EHS5E*" Then EHS5E = EHS5E + 1
If cP Like "*EHS6F*" Then EHS6F = EHS6F + 1
If cP Like "*EHS7G*" Then EHS7G = EHS7G + 1
If cP Like "*EHS8H*" Then EHS8H = EHS8H + 1
If cP Like "*EHS9I*" Then EHS9I = EHS9I + 1
If cP Like "*EHS10J*" Then EHS10J = EHS10J + 1
If cP Like "*EHS11K*" Then EHS11K = EHS11K + 1
If cP Like "*EHS12L*" Then EHS12L = EHS12L + 1

    Next cP
    
    For Each dP In Range(Cells(18, 16), Cells(aRow, 16)).SpecialCells(xlCellTypeVisible)
        If dP Like "*ALL*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E1").Value = ALL & ":   TOTAL INCIDENTS SHOWN WITHIN THE DATE RANGE SELECTED"
If dP Like "*CSR0o*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E2").Value = CSR0o & ":   TOTAL CSR RELATED INCIDENTS SHOWN"
If dP Like "*CSR21U*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E3").Value = CSR21U & ":   CONTAMINATION (internal) Incident Types shown."
If dP Like "*CSR22V*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E4").Value = CSR22V & ":   MECHANICAL EQUIPMENT/FAILURE Incident Types shown."
If dP Like "*CSR23W*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E5").Value = CSR23W & ":   DOCUMENT/RECORD/PAPERWORK Incidents"
If dP Like "*CSR24X*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E6").Value = CSR24X & ":   CUSTOMER OWNED Incident Types shown."
If dP Like "*CSR25Y*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F7").Value = CSR25Y & ":   CSR Miscellaneous issues."
If dP Like "*SEA0o*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E8").Value = SEA0o & ":   INTERNAL INCIDENTS SHOWN (not reported by customers.)"
If dP Like "*MIS0o*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E9").Value = MIS0o & ":   MISCELLANEOUS INCIDENTS SHOWN (not reported by customers.)"
If dP Like "*EHS0o*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E10").Value = EHS0o & ":   TOTAL NUMBER OF E-H-S INCIDENTS."
If dP Like "*EHS1A*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E11").Value = EHS1A & ":   EHS INCIDENTS FOR:  injuries."
If dP Like "*SEA2B*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E12").Value = SEA2B & ":   PROCESS DEVIATIONS shown."
If dP Like "*SEA1A*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E13").Value = SEA1A & ":   PROCESS FAILURES shown."
If dP Like "*MIS7G*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E14").Value = MIS7G & ":   MDR rosenberg types shown."
If dP Like "*MIS8H*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E15").Value = MIS8H & ":   MDR el campo types shown."
'******************************************************************************************
If dP Like "*CSR1A*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F1").Value = CSR1A & ":   HAZARDOUS LABELING Incident Types shown."
If dP Like "*CSR2B*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F2").Value = CSR2B & ":   WRONG OR INCORRECT LABEL Incident Types shown."
If dP Like "*CSR3C*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F3").Value = CSR3C & ":   MISSING LABEL Incident Types shown."
If dP Like "*CSR4D*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F4").Value = CSR4D & ":   LABEL PLACEMENT Incident Types shown."
If dP Like "*CSR5E*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F5").Value = CSR5E & ":   LIDS OR Incident Types shown."
If dP Like "*CSR6F*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F6").Value = CSR6F & ":   INCORRECT PACKAGING Incident Types shown."
If dP Like "*CSR7G*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F7").Value = CSR7G & ":   PACKAGING FAILURE Incident Types shown."
If dP Like "*CSR8H*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F8").Value = CSR8H & ":   MIXED PRODUCTS Incident Types shown."
If dP Like "*CSR9I*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F9").Value = CSR9I & ":   PRODUCT Leaking Incident Types shown."
If dP Like "*CSR10J*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F10").Value = CSR10J & ":   OFF COLOR or WRONG COLOR Incident Types shown."
If dP Like "*CSR11K*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F11").Value = CSR11K & ":   PERFORMANCE ISSUE Incident Types shown."
If dP Like "*CSR12L*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F12").Value = CSR12L & ":   CONTAMINATION Incidents"
If dP Like "*CSR13M*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F13").Value = CSR13M & ":   (APPEARANCE) doesn't look right Incident Types shown."
If dP Like "*CSR14N*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F14").Value = CSR14N & ":   BILLING AND/OR Incident Types shown."
If dP Like "*CSR15O*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F15").Value = CSR15O & ":   MISSED SHIPMENT Incident Types shown."
If dP Like "*CSR16P*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F16").Value = CSR16P & ":   SLOPPY WORKMANSHIP Incident Types shown."
If dP Like "*CSR17Q*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F17").Value = CSR17Q & ":   CARRIER/SHIPPER Incidents"
If dP Like "*CSR18R*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F18").Value = CSR18R & ":   RECEIVED INCORRECT product Incident Types shown."
If dP Like "*CSR19S*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F19").Value = CSR19S & ":   MATERIAL REMAINING in tank/Container Incident Types."
If dP Like "*CSR20T*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F20").Value = CSR20T & ":   WRONG PRODUCT Incident Types shown."
If dP Like "*CSR26ZZ*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F22").Value = CSR26ZZ & ":   customer received damaged products issue."
If dP Like "*CSR27AA*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F23").Value = CSR27AA & ":   customer received wrong product incidents."
If dP Like "*CSR28BB*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F24").Value = CSR28BB & ":   order entry or chempax issues."
If dP Like "*CSR29CC*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F25").Value = CSR29CC & ":   scheduling issues incidents."
If dP Like "*CSR30DD*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F26").Value = CSR30DD & ":   product shipped w/ wrong paperwork issues."
If dP Like "*CSR31EE*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F27").Value = CSR31EE & ":   raw material issues (COP incidents only)."
If dP Like "*CSR32FF*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("F28").Value = CSR32FF & ":   issues regarding inventory related incidents."
'******************************************************************************************
If dP Like "*SEA3C*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G1").Value = SEA3C & ":   all contamination incidents ."
If dP Like "*SEA4D*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G2").Value = SEA4D & ":   mechanical or equip failures ."
If dP Like "*SEA5E*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G3").Value = SEA5E & ":   document paperwork incidents ."
If dP Like "*SEA6F*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G4").Value = SEA6F & ":   raw material issues (just material or products)."
If dP Like "*SEA7G*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G5").Value = SEA7G & ":   raw material issues (NOT material or products)."
If dP Like "*SEA8H*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G6").Value = SEA8H & ":   customer owned material issues ."
If dP Like "*SEA9I*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G7").Value = SEA9I & ":   customer owned(not product) ."
If dP Like "*SEA10J*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G8").Value = SEA10J & ":   leaking hose pipe fitting incidents ."
If dP Like "*SEA11K*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G9").Value = SEA11K & ":   workmanship issues ."
If dP Like "*SEA12L*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G10").Value = SEA12L & ":   inventory incidents ."
If dP Like "*SEA13M*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G11").Value = SEA13M & ":   carrier/shipper incidents ."
If dP Like "*SEA14N*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G12").Value = SEA14N & ":   other (not listed) ."
If dP Like "*SEA15O*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G13").Value = SEA15O & ":   hazardous labels or placard issues ."
If dP Like "*SEA16P*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("G14").Value = SEA16P & ":   packaging related issues ."
'******************************************************************************************
If dP Like "*MIS1A*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("H1").Value = MIS1A & ":   SUPPLIER RELATED Incident Types shown."
If dP Like "*MIS2B*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("H2").Value = MIS2B & ":   CONTRACTOR Incidents"
If dP Like "*MIS3C*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("H3").Value = MIS3C & ":   LEAKING HOSE/PIPE/FITTING Incident Types shown."
If dP Like "*MIS4D*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("H4").Value = MIS4D & ":   WORKMANSHIP (internal) Incident Types shown."
If dP Like "*MIS5E*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("H5").Value = MIS5E & ":   INVENTORY Incidents"
If dP Like "*MIS6F*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("H6").Value = MIS6F & ":   CARRIER / Incident Types shown."
If dP Like "*MIS9I*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("H7").Value = MIS9I & ":   PERSONNEL ISSUE/EMPLOYEE Incident Types shown."
If dP Like "*MIS10J*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("H8").Value = MIS10J & ":   OTHER/NOT LISTED Incident Types shown."
'******************************************************************************************
If dP Like "*EHS2B*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I1").Value = EHS2B & ":   NEAR MISS Incident Types shown."
If dP Like "*EHS3C*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I2").Value = EHS3C & ":   SLIP, TRIP, Incident Types shown."
If dP Like "*EHS4D*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I3").Value = EHS4D & ":   STRUCK BY Incident Types shown."
If dP Like "*EHS5E*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I4").Value = EHS5E & ":   EXPOSURE (ehs) Incident Types shown."
If dP Like "*EHS6F*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I5").Value = EHS6F & ":   SPLASHED W Incident Types shown."
If dP Like "*EHS7G*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I6").Value = EHS7G & ":   RESPIRATOR (ehs) Incident Types shown."
If dP Like "*EHS8H*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I7").Value = EHS8H & ":   MOBILE INCIDENT(FORK-LIFT) Incident Types shown."
If dP Like "*EHS9I*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I7").Value = EHS9I & ":   GAS EXCURSION Incident Types shown."
If dP Like "*EHS10J*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I7").Value = EHS10J & ":   OTHER (ehs) Incident Types shown."
If dP Like "*EHS11K*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I10").Value = EHS11K & ":   SPILLS (ehs) Incident Types shown."
If dP Like "*EHS12L*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I11").Value = EHS12L & ":   PPE (ehs) Incident Types shown."
If dP Like "*EHS13M*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("I12").Value = EHS13M & ":   ??? (ehs) Incident Types shown."

     Next dP

' COPY PREVIOUSLY CALCULATED DATA BUT REMOVE BLANK ROWS FROM EACH COLUMN:
' **********************************************************************************************************************************************
    MsgBox "Here"
'COLUMN E:  TOTALS COLUMNS
ActiveWorkbook.Worksheets("ANALYTICS").Activate
ActiveWorkbook.Worksheets("ANALYTICS").Range("E1:E16").SpecialCells(xlCellTypeConstants).Select
Selection.Copy
ActiveWorkbook.Worksheets("ANALYTICS").Range("J1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    MsgBox "Here2"
'COLUMN F:  CSR COLUMN
ActiveWorkbook.Worksheets("ANALYTICS").Activate
ActiveWorkbook.Worksheets("ANALYTICS").Range("F1:F21").SpecialCells(xlCellTypeConstants).Select
Selection.Copy
ActiveWorkbook.Worksheets("ANALYTICS").Range("K1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
     MsgBox "Here3"
'COLUMN G:  SEATEX INTERNAL INCIDENTS COLUMN
ActiveWorkbook.Worksheets("ANALYTICS").Activate
ActiveWorkbook.Worksheets("ANALYTICS").Range("G1:G16").SpecialCells(xlCellTypeConstants).Select
Selection.Copy
ActiveWorkbook.Worksheets("ANALYTICS").Range("L1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
      MsgBox "Here4"
'COLUMN H:  MISCELLANEOUS INCIDENTS COLUMN
ActiveWorkbook.Worksheets("ANALYTICS").Activate
ActiveWorkbook.Worksheets("ANALYTICS").Range("H1:H16").SpecialCells(xlCellTypeConstants).Select
Selection.Copy
ActiveWorkbook.Worksheets("ANALYTICS").Range("M1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
     MsgBox "Here5"
'COLUMN I:  E-H-S INCIDENTS COLUMN
ActiveWorkbook.Worksheets("ANALYTICS").Activate
ActiveWorkbook.Worksheets("ANALYTICS").Range("I1:I16").SpecialCells(xlCellTypeConstants).Select
Selection.Copy
ActiveWorkbook.Worksheets("ANALYTICS").Range("N1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
     MsgBox "Here6"

' CODE FOR LOOKING AT THE TOTAL TALLY FOR EACH INCIDENT CODE(TYPE) AND THEN COPYING EACH TOTAL OVER ONTO THE REPORTS WORKSHEET TO BE VIEWED
'**********************************************************************************************************************************************
'**********************************************************************************************************************************************
'**********************************************************************************************************************************************
'**********************************************************************************************************************************************
'**********************************************************************************************************************************************
'**********************************************************************************************************************************************
'**********************************************************************************************************************************************
Dim jP As Variant
Dim lP As Variant
Dim mP As Variant
Dim nP As Variant

Dim cntRowsJ As Long

Dim cntRowsK As Long

Dim cntRowsL As Long
Dim cntRowsM As Long
Dim cntRowsN As Long

cntRowsJ = ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 10), Cells(20, 10)).UsedRange.Rows.Count
cntRowsK = ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 11), Cells(20, 11)).UsedRange.Rows.Count
cntRowsL = ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 12), Cells(20, 12)).UsedRange.Rows.Count
cntRowsM = ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 13), Cells(20, 13)).UsedRange.Rows.Count
cntRowsN = ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 14), Cells(20, 15)).UsedRange.Rows.Count
MsgBox cntRowsJ
MsgBox cntRowsK
MsgBox cntRowsL
MsgBox cntRowsM
MsgBox cntRowsN
'**********************************************************************************************************************************************
'**********************************************************************************************************************************************
For Each jP In Range(Cells(1, 10), Cells(cntRowsJ, 10))
MsgBox cntRowsJ
MsgBox "1"
' TOTAL INCIDENT TALLY
' COPY THE ROWS IN THE "TOTAL" COLUMN (ON THE ANALYTICS SHEET) AND PASTE ONTO THE TOTAL COLUMN (A2) ON THE "REPORTS" WORKSHEET:
    If cntRowsJ >= 1 Then
    
    'copies just the rows in column J and pastes them on the REPORTS worksheet starting with cell A2.
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
    ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 10), Cells(cntRowsJ, 10)).Select
    Selection.Copy
    ActiveWorkbook.Worksheets("REPORTS").Activate
    ActiveWorkbook.Worksheets("REPORTS").Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
   
   Else: Exit Sub
    End If
Next
'**********************************************************************************************************************************************
For Each kP In Range(Cells(1, 11), Cells(cntRowsK, 11))
' CUSTOMER SERVICE INCIDENT TALLY
' COPY THE ROWS IN THE "CUSTOMER SERVICE (CSR)" COLUMN (ON THE ANALYTICS SHEET) AND PASTE ONTO THE TOTAL COLUMN (D2) ON THE "REPORTS" WORKSHEET:
    If cntRowsK >= 1 Then
    
    'copies just the rows in column K and pastes them on the REPORTS worksheet starting with cell D2.
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
    ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 11), Cells(15, 11)).Select
    Selection.Copy
    ActiveWorkbook.Worksheets("REPORTS").Activate
    ActiveWorkbook.Worksheets("REPORTS").Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWorkbook.Worksheets("ANALYTICS").Activate

   Else: Exit Sub
    End If
Next
'**********************************************************************************************************************************************
For Each lP In Range(Cells(1, 12), Cells(cntRowsL, 12))

MsgBox cntRowsL
' INTERNAL SEATEX INCIDENT TALLY
' COPY THE ROWS IN THE "SEATEX INTERNAL" COLUMN (ON THE ANALYTICS SHEET) AND PASTE ONTO THE TOTAL COLUMN (A2) ON THE "REPORTS" WORKSHEET:
    If cntRowsL >= 1 Then
    
    'copies just the rows in column L and pastes them on the REPORTS worksheet starting with cell H2.
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
    ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 12), Cells(15, 12)).Select
    Selection.Copy
    ActiveWorkbook.Worksheets("REPORTS").Activate
    ActiveWorkbook.Worksheets("REPORTS").Range("H2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWorkbook.Worksheets("ANALYTICS").Activate

   Else: Exit Sub
    End If
Next
'**********************************************************************************************************************************************
For Each mP In Range(Cells(1, 13), Cells(cntRowsM, 13))
' MISCELLANEOUS INCIDENT TALLY
' COPY THE ROWS IN THE "MISCELLANEOUS COLUMN (ON THE ANALYTICS SHEET) AND PASTE ONTO THE TOTAL COLUMN (A2) ON THE "REPORTS" WORKSHEET:
    If cntRowsM >= 1 Then
    
    'copies just the rows in column M and pastes them on the REPORTS worksheet starting with cell I2.
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
    ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 13), Cells(15, 13)).Select
    Selection.Copy
    ActiveWorkbook.Worksheets("REPORTS").Activate
    ActiveWorkbook.Worksheets("REPORTS").Range("I2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
   
   Else: Exit Sub
    End If
Next
'**********************************************************************************************************************************************
For Each nP In Range(Cells(1, 14), Cells(cntRowsN, 14))
' E-H-S INCIDENT TALLY
' COPY THE ROWS IN THE "EHS" COLUMN (ON THE ANALYTICS SHEET) AND PASTE ONTO THE TOTAL COLUMN (A2) ON THE "REPORTS" WORKSHEET:
    If cntRowsN >= 1 Then
    
    'copies just the rows in column N and pastes them on the REPORTS worksheet starting with cell J2.
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
    ActiveWorkbook.Worksheets("ANALYTICS").Range(Cells(1, 14), Cells(15, 14)).Select
    Selection.Copy
    ActiveWorkbook.Worksheets("REPORTS").Activate
    ActiveWorkbook.Worksheets("REPORTS").Range("J2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWorkbook.Worksheets("ANALYTICS").Activate
   
   Else: Exit Sub
    End If
Next
'**********************************************************************************************************************************************
'**********************************************************************************************************************************************
'**********************************************************************************************************************************************
'UNFILTER THE "SEATEX INCIDENT LOG" WORKSHEET FROM WHERE IT WAS PREVISOULY FILTERED TO SHOW ONLY THE ROWS THAT FALL BETWEEN 2 DATES
'**********************************************************************************************************************************************

With ws
    ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
    ActiveSheet.Range(Cells(17, 1), Cells(rCol, 49)).AutoFilter Field:=2
    ActiveWorkbook.Worksheets("Seatex Incident Log").Range(Cells(rCol, 1), Cells(rCol, 1)).Select
End With
'REDIRECT FOCUS BACK TO THE "REPORTS" WORKSHEET
'**********************************************************************************************************************************************
        ActiveWorkbook.Worksheets("REPORTS").Activate
        ActiveWorkbook.Worksheets("REPORTS").Visible = True
        ActiveWorkbook.Worksheets("REPORTS").Range("A18").Select

With ActiveWorkbook.Worksheets("REPORTS")
    ActiveWindow.ScrollIntoView Left:=0, Top:=0, Width:=100, Height:=100
    Cells(18, 1).Select
    ActiveWindow.ScrollColumn = ActiveCell.Column
End With

bm_Error_norecords:
'MsgBox "Sorry, no Incidents were found"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

bFlag = True
Unload Me

End Sub
'**********************************************************************************************************************************************
Private Sub cmdClose_Click()
Dim ctrl As MSForms.Control
 Dim x As Long
bns = MsgBox("Are you sure you want to close?  All previously entered data will be lost.", vbYesNo)
If bns = vbYes Then: Unload Me
Else

Unload Me
End If

End Sub

Private Sub frmReportCriteria_terminate()
Worksheets("Seatex Incident Log").Activate
    Unload Me
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top