Run A Procedure Before Losing A Workbook

You can create a procedure that runs automatically before a particular workbook closes in Excel. Because this type of procedure only executes once as the workbook closes, it works well for removing custom menus and toolbars loaded when the workbook opened, closing other workbooks, recalculating, or even automatically saving the workbook. The procedure executes when the workbook closes by catching the BeforeClose event that the closing workbook triggers.

To produce a procedure that executes when a workbook closes, you create a new procedure and add it to the ThisWorkbook object code module for the particular workbook. In fact, all event-handling procedures that you create for monitoring workbook events must reside within the ThisWorkbook object in order for Excel to execute them automatically. To create a procedure that executes when a workbook closes, you name the procedure Workbook_BeforeClose.

Although the procedure resides in the ThisWorkbook object code module, it can access other procedures within the same workbook. Therefore, you can create a Workbook_BeforeClose procedure that calls procedures located in another module.

If you have a procedure that you want Excel to execute whenever an application closes, you must place the procedure within the ThisWorkbook object for the Personal Macro Workbook, Personal.xls. Because the Personal Macro Workbook always loads as a hidden workbook in Excel, and typically only closes when you close Excel, any procedures within this workbook appear to execute as Excel closes. Keep in mind that Excel associates the Personal Macro Workbook with an individual user.

The BeforeClose event has one parameter, Cancel, that Excel passes to the procedure when the event is triggered. You can change what Excel does after the BeforeClose event completes by changing the value of the Cancel parameter. If the Cancel parameter has a value of False, which is the default, the workbook closes as normal. If you set the value of the Cancel parameter to True, Excel does not close the workbook and cancels the closing process.

RUN A PROCEDURE BEFORE CLOSING A WORKBOOK

File Edit View Insert Format

g VBAProject (Chapl5-Mac|

5 Microsoft Excel Objects Chartl (Chartl) U Sheetl (Sheet 1) :2 (Sheet2)

File Edit View Insert Format

g VBAProject (Chapl5-Mac|

ThisWorkbook Workbook 3 Alphabetic | Categorized |

ThisWorkbook Workbook 3 Alphabetic | Categorized |

AutoUpdateFreq 0 ¡eHistorypjo ConflictResolutio 1 - xlUserResol Datel904 False DispiayDrawingC -4104 - xlDispla EnableAutoReco True jpe Visible | False_„

Private Sub Workbook_Open() Uorltboolrs. Open "Chapll-Hacroj End Sub

LB On the Projects window, locate the workbook where you want to add the Workbook_BeforeClose subroutine.

Activate

Addininstaii

AddinUninstall

Deactivate

NewSheet

PivotTsbieCioseConnection

0 Double-click the ThisWorkbook object node under the workbook.

VBAProject (Chapl5-Mac

Microsoft Excel Objects Chartl (Chartl) Sheetl (Sheetl) —Sheet2 (Sheet2) Sheet3 (Sheet3) L-S ThisWorkbook Modules Module 1 -«It Module2 -4t Module3 L-Ä Module! à-ID Class Modules

ThisWorkbook Workbook 3 Alphabetic | Categorized |

AutoUpdateFreq 0 jeHistoryDiO ConflictResolutio 1 - xlUserResol Datel9Q4 False DisplayDrawingC -4104 - xlDispla EnableAutoReco True

3peVisible | False_ 5

Activate

Addininstaii

AddinUninstall

Deactivate

NewSheet

PivotTsbieCioseConnection

~~0 In the Object box, click Q and then click the Workbook option.

□ In the Procedure box, click Q and then the BeforeClose option.

AUTOMATING PROCEDURES WITH EXCEL EVENTS

You can use the Me operator to work in a code module for a specific Excel object. When you use the Me operator, it references the object related to the code module. For example, if you create code in the ThisWorkbook object module, all code in the module correlates to the actual workbook object. When you use the Me operator, you reference the workbook object. Therefore, when you add the code Me.Close to a code module, Excel closes the corresponding workbook.

The code Me.Close is equivalent to using the ThisWorkbook object reference.

Keep in mind that with the ThisWorkbook object code module, you can use either the Me object or the ThisWorkbook object to reference the current workbook, a condition not true in a standard code module. If you create a code module that you do not associate with an object, you cannot use the Me operator to reference an object without generating an error.

You can also use the Me operator when working with UserForm code modules. In doing so, the Me operator references the corresponding UserForm and not the controls that you have added to the UserForm.

-■ The Visual Basic Editor creates a new Private subroutine named Workbook BeforeClose.

0 Type the VBA code to run before the workbook closes.

! □ g? y B s

â aa^ *

m

I -

& = -

na c

3

- ,0 , B

/ U 3=

S E=

L_

C1

ß 41

I

n

A

B

IJ

I D

E

F

G

H

I

J

K

Miami

241

t

Z3Ï!

I 21

25

18

2

New York

31

54*

5

17

24

3

Los Angeles

51

46

34

21

10

4

Denver

23

31

11

31

21

5

Chicago

4

52

22\ 22

21

6

Detroit

22

36

32

13

16

7

Las Vegas

11

44

19

9

8

8

9

10

Doyou

13

want to close

workbook Chapi 5-Macros.

14

15

~Yes I

Ne, |

16

-1 -

JZ_

18

19

20.

21

22

23

24

25

26

27

_^^

_

_

_

r

f

-■ The Visual Basic Editor creates a new Private subroutine named Workbook BeforeClose.

0 Type the VBA code to run before the workbook closes.

■ The Workbook_BeforeClose procedure executes the specified VBA code as the workbook before closing the workbook.

0 0

Post a comment