Loop to check when workbook close

Question

The code below checks specific columns to see if their rows have similar values. If yes, in column 57 it sums up their values and if the total is less then 100 the cell turns red. How can I make this code to work only when you close the workbook? I mean I want the loop to work only when people are closing the Excel file.

I know that I have to change something in Private Sub Worksheet_Change(ByVal Target As Range), but if I do that, then the code won't work. For the code to work it has to be Worksheet_Change. Or can i call this code under a button? Have a good day!

Thanks in advance!

Private Sub Worksheet_Change(ByVal Target As Range)


Dim TestRow As Integer
Dim TestColumn As Integer
Dim Sum As Integer


TestRow = Target.row
TestColumn = Target.Column

    If (TestColumn = 57) Then


        If (TestRow > 5) Then
            Sum = 0
            EColumnValue = 0
            FColumnValue = 0
            IColumnValue = 0
            APColumnValue = 0
            AQColumnValue = 0

            For i = TestRow To 5 Step -1

                If (i = TestRow) Then

                    Sum = ThisWorkbook.Sheets("Close").Cells(TestRow, 57).Value
                    EColumnValue = ThisWorkbook.Sheets("Close").Cells(TestRow, 4).Value
                    FColumnValue = ThisWorkbook.Sheets("Close").Cells(TestRow, 5).Value
                    IColumnValue = ThisWorkbook.Sheets("Close").Cells(TestRow, 8).Value
                    APColumnValue = ThisWorkbook.Sheets("Close").Cells(TestRow, 36).Value
                    AQColumnValue = ThisWorkbook.Sheets("Close").Cells(TestRow, 37).Value
                Else

                    If (EColumnValue = ThisWorkbook.Sheets("Close").Cells(i, 4).Value) And (FColumnValue = ThisWorkbook.Sheets("Close").Cells(i, 5).Value) And (IColumnValue = ThisWorkbook.Sheets("Close").Cells(i, 8).Value) And (APColumnValue = ThisWorkbook.Sheets("Close").Cells(i, 36).Value) And (AQColumnValue = ThisWorkbook.Sheets("Close").Cells(i, 37).Value) Then
                        Sum = Sum + ThisWorkbook.Sheets("Close").Cells(i, 57).Value

                        If Sum < 100 Then

                           ThisWorkbook.Sheets("Close").Cells(TestRow, 57).Interior.Color = RGB(255, 0, 0)


           Else
                           ThisWorkbook.Sheets("Close").Cells(TestRow, 57).Interior.Color = RGB(255, 255, 255)

                            End If


                    End If
                End If
           Next

        End If
    End If

Andy G Andy G · Accepted Answer · 2018-01-05T09:11:47

In the Project Explorer (Ctrl-R) double-click ThisWorkbook, then from the editor window switch the first drop-down from (General) to Workbook. The second drop-down then provides access to workbook-level events.

From the second drop-down select BeforeClose to generate its procedure stub. (You can remove the empty default stub for Workbook_Open.)

BeforeClose does not provide you with the Target parameter because it is not relevant to closing.

Loop to check when workbook close

1 Answers