Run macro brfore close

[Al]

Hello
I would like to run this code before the file is closed.

Sub Update()

Msg = "Update File?"
Title = "File Update"
Response = MsgBox(Msg, vbYesNoCancel + vbQuestion, Title)
If Response = vbNo Then
Exit Sub
End If

If Response = vbCancel Then
Exit Sub
End If

Run file update code

MsgBox "File Updated"


End Sub

How do I do this?
Thanks

 

[Bob Umlas]

In the VBE, double-click the ThisWorkbook in the Project window, enter this
code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
update 'runs your macro
End Sub

Bob Umlas
Excel MVP

 

[Jacob Skaria]

From workbook launch VBE using short-key Alt+F11. On the left treeview for
this project double click 'This Workbook' and paste the code to the code
panel.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Update File?", vbYesNoCancel + vbQuestion, "File Update") _
<> vbYes Then Exit Sub
'Run file update code
MsgBox "File Updated"
End Sub

If this post helps click Yes

 

[ryguy7272]

Bob is right, put your macro in ThisWorkbook. I did this this for a recent
project, to delete any pivot tables that users created and I called another
macro, to make all sheets, but one, hidden, before the workbook closed. Here
is the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.TableRange2.Clear
Next pt
Next ws

Call HideSheets
End Sub

HTH,
Ryan---

 

[john]

Code below needs to be placed in This WorkBook - From workbook launch VBE
using short-key Alt+F11. On the left treeview for
this project double click This Workbook and paste the code there.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

msg = "Update File?"
Title = "File Update"

response = MsgBox(msg, vbYesNoCancel + vbQuestion, Title)

If response <> vbNo Then

'exit without saving
Saved = True

ElseIf response = vbCancel Then

'cancel close
Cancel = True

Exit Sub

Else

'run update your procedure
Run fileupdatecode

msg = MsgBox("File Updated", vbInformation, Title)

'line below assumes your file update code saves changes?
'delete the line if it does not otherwise workbook
'closes without saving the changes
Saved = True

End If


End Sub

 

[john]

sorry, minor typo earlier in code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

msg = "Update File?"
Title = "File Update"

response = MsgBox(msg, vbYesNoCancel + vbQuestion, Title)

If response = vbNo Then

'exit without saving
Saved = True

ElseIf response = vbCancel Then

'cancel close
Cancel = True

Exit Sub

Else

'run update your procedure
Run fileupdatecode

msg = MsgBox("File Updated", vbInformation, Title)

'line below assumes your file update code saves changes?
'delete the line if it does not otherwise workbook
'closes without saving the changes
Saved = True

End If


End Sub

 

[Al]

Thanks!

From workbook launch VBE using short-key Alt+F11. On the left treeview for
this project double click 'This Workbook' and paste the code to the code
panel.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Update File?", vbYesNoCancel + vbQuestion, "File Update") _
<> vbYes Then Exit Sub
'Run file update code
MsgBox "File Updated"
End Sub

If this post helps click Yes

 

[Al]

Thanks!

In the VBE, double-click the ThisWorkbook in the Project window, enter this
code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
update 'runs your macro
End Sub

Bob Umlas
Excel MVP