Run a macho when closing the Excel file

[Lccs]

Hi everyone!

I have this macro and I want to run it when the user closes the file. How can I put it running automatically?

Private Sub Workbook_BeforeClose(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    last_row_ose = get_last_row("S1", "I")
    Worksheets("S1").Range("I12:O" & last_row_ose).ClearContents
    
    last_row_pa = get_last_row("S2", "C")
    Worksheets("S2").Range("C9:G" & last_row_pa).ClearContents
    
    last_row_pro = get_last_row("S3", "C")
    Worksheets("S3").Range("C7:AZ" & last_row_pro).ClearContents
    
    last_row_pr = get_last_row("S4", "C")
    Worksheets("S4").Range("C8:AZ" & last_row_pr).ClearContents
    
    last_row_to = get_last_row("S5", "C")
    Worksheets("S5").Range("C6:H" & last_row_to).ClearContents
          
End Sub

Thanks in advance for your help.

 

[Joe4]

Welcome to the Board!

It will run automatically before closing, provided that:
1. You place this in the "ThisWorkbook" module (it MUST be in this module to run automatically)
2. Macros/VBA are enabled

You may also want to add a "Save" command to the end of that code, so those changes are saved!
(There is not much point in making changes before closing the file if you are not saving those changes).

Just add a line before the "End Sub" line like:

ActiveWorkBook.Save

 

[Lccs]

Welcome to the Board!

It will run automatically before closing, provided that:
1. You place this in the "ThisWorkbook" module (it MUST be in this module to run automatically)
2. Macros/VBA are enabled

You may also want to add a "Save" command to the end of that code, so those changes are saved!
(There is not much point in making changes before closing the file if you are not saving those changes).

Just add a line before the "End Sub" line like:

ActiveWorkBook.Save

Thank you so much for your help!

I had no idea that only in the "ThisWorkbook" module it would be possible to run stuff automatically

 

[Joe4]

You are welcome.

I had no idea that only in the "ThisWorkbook" module it would be possible to run stuff automatically

That is not quite the whole story.

VBA code that runs automatically is called "Event Procedure" code. The code runs automatically upon some "event" happening, like the closing of a workbook or the updating of certain cells.
There are two types of event procedure code:
1. Workbook level, which are found in the "ThisWorkbook" module.
2. Worksheet level, which are found in each "Sheet" module.

In order for it to happen automatically, not only must be it placed in one of those places, it also MUST be named a certain way (you CANNOT name the procedure anything you like).
Any code placed in a Standard/General will not run automatically, no matter what you call it.

 

[Lccs]

You are welcome.

That is not quite the whole story.

VBA code that runs automatically is called "Event Procedure" code. The code runs automatically upon some "event" happening, like the closing of a workbook or the updating of certain cells.
There are two types of event procedure code:
1. Workbook level, which are found in the "ThisWorkbook" module.
2. Worksheet level, which are found in each "Sheet" module.

In order for it to happen automatically, not only must be it placed in one of those places, it also MUST be named a certain way (you CANNOT name the procedure anything you like).
Any code placed in a Standard/General will not run automatically, no matter what you call it.

I really appreciate your detailed explanation! Thank you! I am new with VBA so I have a lot of things to learn

 

[Joe4]

You are quite welcome.
Glad I was able to help.