| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro brfore close
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run macro brfore close
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 "Al" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run macro brfore close
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 --------------- Jacob Skaria "Al" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run macro brfore close
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Bob Umlas" wrote: 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 "Al" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run macro brfore close
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 -- jb "Al" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run macro brfore close
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 -- jb "john" wrote: 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 -- jb "Al" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run macro brfore close
Thanks!
"Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Al" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run macro brfore close
Thanks!
"Bob Umlas" wrote: 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 "Al" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I stop the close method in an auto close macro | Excel Programming | |||
| Don't let user close. Use macro button to close? | Excel Programming | |||
| Do I have to close an odc in a macro? | Excel Programming | |||
| Run Macro on close | Excel Programming | |||
| On Close macro | Excel Programming | |||
Linear Mode
