Run A Procedure Before Saving A Workbook

You can create a procedure that runs automatically before Excel saves a particular workbook. By creating this type of procedure, you can customize the method you use to save the workbook. For example, you may always want to display the Save As dialog box whenever the user selects the Save or SaveAs option in Excel. This procedure executes whenever you select the Save or the SaveAs options within Excel for the corresponding workbook.

To create a procedure that executes before saving a workbook, you create a new procedure using the BeforeSave event 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 to have Excel execute them automatically. To create a procedure that executes before Excel saves the workbook, you name the procedure Workbook_BeforeSave.

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_BeforeSave procedure that calls procedures located in another code module within the same workbook.

The BeforeSave event has two parameters that Excel passes to your procedure when the event triggers. The SaveUI parameter indicates whether the Save As dialog box displays during the Save command. Set the value of the SaveUI parameter to True to always display the Save As dialog box. If the Cancel parameter has a value of False, Excel saves the workbook. If you set the value of the Cancel parameter to True, Excel does not save the workbook. Within the Workbook_BeforeSave procedure you can change the value of the Cancel parameter to specify whether the workbook actually saves.

RUN A PROCEDURE BEFORE SAVING A WORKBOOK

jTypea question for help |t _ fii :

ÉHfS Microsoft Excel Objects I-® Chartl (Chartl) —Ig] Sheet 1 (Sheet 1) -fi Sheet2(Sheet2)

ThisWorkbook 'A Alphabetic | Categorized |

AcceptLabelsInF False AutoUpdateFreq 0

ConflictResolutio 1 - xlUserResol Datei 904 False DisplayDrawingC -4104 - xlDispls

(Workbook

"3

e(Cancel As Boolean)

UserResponse = HsgBox("Do you i If UserResponse = vbNo Then

Cancel = Tcue End If

_If TJgecRegppnse = vbYes

He.Save End If End Sub

Private Sub Uoi:]tbook_0pen < Uorktiooks.Open "Chapl4-Hac: End Sub

^Q On the Projects window, locate the workbook where you want to add the Workbook_BeforeSave subroutine.

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

i VBAPtoject (ChapL5-Mac

. Microsoft E.cel Objects Chartl (Chartl)

ThisWorkbook Alphabetic |

AcceptLabelsInF False AutoUpdateFreq 0

ConflictResolutio 1 - xlUserResol Datei 904 False DisplayDrawingC -4104 - xlDlspls

End If

If UserRespoi

Me.Save End If End Sub i = HsgBox("Do you we iponse = vbNo Then . = True

L0 In the Object box, click 3 and then the Workbook option.

Deactivate NewSheet Open

PivotTableOpenConnection

SheetAotivate

SheetBetoreDoubleClick

SheetBeforeRightCIIck

SheetCalculate

□ In the Procedure box, click Q and then the

BeforeSave option.

AUTOMATING PROCEDURES WITH EXCEL EVENTS

When working with VBA, you use the ByVal keyword to specify that only the value of a variable passes to a procedure. VBA uses the ByVal keyword with some parameters. For example, the Workbook_BeforeSave subroutine includes a SaveAsUI parameter that passes into the subroutine by value only as indicated with the ByVal keyword. Using the ByVal keyword, the value of the parameter passes into the procedure that uses the SaveAsUI variable. If you change the value, it does not affect the actual variable. To better explain this, consider the following example, where the message box displays a value of 10 because the value of TestVal passes in the Test2 subroutine by value. In other words, instead of using the variable TestVal, the Test2 subroutine uses a copy of the original TestVal variable. Any changes you make within the Test2 subroutine do not pass back to the original subroutine, Testl.

Example:

Sub Test1()

Dim TestVal As Integer TestVal = 12 Call Test2(TestVal) MsgBox TestVal End Sub

Sub Test2(ByVal TestVal)

TestVal = TestVal +1 End Sub

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

S Type the VBA code to run before Excel saves the workbook.

-Q Click Save.

¿iew Insert Format Tools Data Window Help a I « a y ü us a ï • it I o m jTypea question for help _ fi1 x

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

S Type the VBA code to run before Excel saves the workbook.

-Q Click Save.

¿iew Insert Format Tools Data Window Help a I « a y ü us a ï • it I o m jTypea question for help _ fi1 x

r~

A

B

E

F

g

h

j

k

Miami

24|

c

4Ï1

1 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

G

Detroit

22

36

32

13

16

7

Las Vegas

11

44

19

9

8

8

9

10

IT

13

uojiouwianuo save m

is workbook Chap15-Macros.

14

15

[

"YÜ ]

n„ 1

16

--

17

18

19

20

2L

22

23

24

25

26

_

27

m -4

h h is Chartl \Sheet 1 / Sheet2 / Sheet3 /

1 "

1 jüj

J

■ The Workbook_BeforeSave procedure executes the specified VBA code before saving the workbook.

0 0

Responses

  • camryn
    How to add a procedure BeforeSave to excel vba?
    5 months ago

Post a comment