Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable "save" option on close.
I would like to disable the "Do you want to save changes..." message after
running a macro when exiting the Excel program. I have a macro that runs and changes the cell in a varies sheets, but I do not want my end user to be able to save the changes. I have setup formulas thought out the sheets and I do not want to load them every time. When I exit out of the program it promotes me to always save. Is there a way to disable this or maybe make my Msgbox button just exit out of the program without saving changes when click on €œOK€? Then on that note, when I want to make changes, will I still be able to save my workbook and if so, how? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable "save" option on close.
You could put this kind of code in the ThisWorkbook module:
Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = True MsgBox "You can't save this!" End Sub But when you want to save, you'll have to turn off events. hit alt-f11 to get to the VBE hit ctrl-g to see the immediate window type this and hit enter application.enableevents = false then save your workbook. And remember to togglet that setting back to True application.enableevents = true So that events are enabled again. PG wrote: I would like to disable the "Do you want to save changes..." message after running a macro when exiting the Excel program. I have a macro that runs and changes the cell in a varies sheets, but I do not want my end user to be able to save the changes. I have setup formulas thought out the sheets and I do not want to load them every time. When I exit out of the program it promotes me to always save. Is there a way to disable this or maybe make my Msgbox button just exit out of the program without saving changes when click on €œOK€? Then on that note, when I want to make changes, will I still be able to save my workbook and if so, how? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable "save" option on close.
This shouldn't be in the _BeforeClose event.
It should be in the _BeforeSave event. Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True MsgBox "You can't save this!" End Sub Sorry about the typo. Dave Peterson wrote: You could put this kind of code in the ThisWorkbook module: Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = True MsgBox "You can't save this!" End Sub But when you want to save, you'll have to turn off events. hit alt-f11 to get to the VBE hit ctrl-g to see the immediate window type this and hit enter application.enableevents = false then save your workbook. And remember to togglet that setting back to True application.enableevents = true So that events are enabled again. PG wrote: I would like to disable the "Do you want to save changes..." message after running a macro when exiting the Excel program. I have a macro that runs and changes the cell in a varies sheets, but I do not want my end user to be able to save the changes. I have setup formulas thought out the sheets and I do not want to load them every time. When I exit out of the program it promotes me to always save. Is there a way to disable this or maybe make my Msgbox button just exit out of the program without saving changes when click on €œOK€? Then on that note, when I want to make changes, will I still be able to save my workbook and if so, how? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why excel close all files when I just want to close one files | Excel Discussion (Misc queries) | |||
close workbook without closing excel and stop recursive function | Excel Discussion (Misc queries) | |||
Disable Tools Protection Protect Sheet for all users but one | Excel Discussion (Misc queries) | |||
Excel shoud not close all active books when clicking close button | Excel Discussion (Misc queries) | |||
Disable "Save As" Option | Excel Discussion (Misc queries) |