Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
What can be done in VBA such that <CTRL<S will be blocked from saving a
spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
You might use the beforesave event:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub If macros are disable, this of course will not work. If you not familiar with events see Chip Pearson's page http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "G Lykos" wrote in message ... What can be done in VBA such that <CTRL<S will be blocked from saving a spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
Tom, thanks for your reply. Yes, intercepting the Save via the associated
event seems logical. Looked at your example below and Pearson's EventSeq.xls. Having parameters for an event Sub triggered automatically upon event occurrence is unfamiliar. Cancel must be an outbound results parameter for the Sub, but what does the UI in SaveAsUI represent as a mnemonic, and where are it and Cancel defined? Is SaveAsUI a common Excel flag that the analysis macro can reset, to be picked up by your event Sub below? Thanks again, George "Tom Ogilvy" wrote in message ... You might use the beforesave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub If macros are disable, this of course will not work. If you not familiar with events see Chip Pearson's page http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "G Lykos" wrote in message ... What can be done in VBA such that <CTRL<S will be blocked from saving a spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
George,
The SaveAsUI (Save As from User Interface -- the FileSave As operation) indicates whether the save was initiated by the user or by code. If by the user, SaveAsUI will be True, otherwise False. The Cancel variable allows you to cancel the save operation. Setting it to True will cancel the save. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "G Lykos" wrote in message ... Tom, thanks for your reply. Yes, intercepting the Save via the associated event seems logical. Looked at your example below and Pearson's EventSeq.xls. Having parameters for an event Sub triggered automatically upon event occurrence is unfamiliar. Cancel must be an outbound results parameter for the Sub, but what does the UI in SaveAsUI represent as a mnemonic, and where are it and Cancel defined? Is SaveAsUI a common Excel flag that the analysis macro can reset, to be picked up by your event Sub below? Thanks again, George "Tom Ogilvy" wrote in message ... You might use the beforesave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub If macros are disable, this of course will not work. If you not familiar with events see Chip Pearson's page http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "G Lykos" wrote in message ... What can be done in VBA such that <CTRL<S will be blocked from saving a spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
SaveAsUI is an argument to the BeforeSave event that indicates whether the
SaveAs Dialog will be displayed or not. Cancel is an argument to the BeforeSave event that is passed in as cancel. If you set it to True, the Save will not occur. From help on the BeforeSave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) SaveAsUi True if the Save As dialog box will be displayed. Cancel False when the event occurs. If the event procedure sets this argument to True, the workbook isn't saved when the procedure is finished. -- Regards, Tom Ogilvy "G Lykos" wrote in message ... Tom, thanks for your reply. Yes, intercepting the Save via the associated event seems logical. Looked at your example below and Pearson's EventSeq.xls. Having parameters for an event Sub triggered automatically upon event occurrence is unfamiliar. Cancel must be an outbound results parameter for the Sub, but what does the UI in SaveAsUI represent as a mnemonic, and where are it and Cancel defined? Is SaveAsUI a common Excel flag that the analysis macro can reset, to be picked up by your event Sub below? Thanks again, George "Tom Ogilvy" wrote in message ... You might use the beforesave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub If macros are disable, this of course will not work. If you not familiar with events see Chip Pearson's page http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "G Lykos" wrote in message ... What can be done in VBA such that <CTRL<S will be blocked from saving a spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
Thanks, Chip!
"Chip Pearson" wrote in message ... George, The SaveAsUI (Save As from User Interface -- the FileSave As operation) indicates whether the save was initiated by the user or by code. If by the user, SaveAsUI will be True, otherwise False. The Cancel variable allows you to cancel the save operation. Setting it to True will cancel the save. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "G Lykos" wrote in message ... Tom, thanks for your reply. Yes, intercepting the Save via the associated event seems logical. Looked at your example below and Pearson's EventSeq.xls. Having parameters for an event Sub triggered automatically upon event occurrence is unfamiliar. Cancel must be an outbound results parameter for the Sub, but what does the UI in SaveAsUI represent as a mnemonic, and where are it and Cancel defined? Is SaveAsUI a common Excel flag that the analysis macro can reset, to be picked up by your event Sub below? Thanks again, George "Tom Ogilvy" wrote in message ... You might use the beforesave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub If macros are disable, this of course will not work. If you not familiar with events see Chip Pearson's page http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "G Lykos" wrote in message ... What can be done in VBA such that <CTRL<S will be blocked from saving a spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
Thanks, Tom. Since the analysis Sub and the suggested event Sub both load
at Excel startup (or at minimum at workbook open), then I need a mechanism for the analysis Sub to signal the event Sub to block the Save only if/when analysis has run. How/where would you suggest defining the flag? It needs to exist from Workbook open to close (is persistent the right word?), be writable by the analysis Sub and readable by the event Sub, and have a "correct" initial value (interpreted as not block a Save) without the analysis Sub having run. Thanks again, George "Tom Ogilvy" wrote in message ... SaveAsUI is an argument to the BeforeSave event that indicates whether the SaveAs Dialog will be displayed or not. Cancel is an argument to the BeforeSave event that is passed in as cancel. If you set it to True, the Save will not occur. From help on the BeforeSave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) SaveAsUi True if the Save As dialog box will be displayed. Cancel False when the event occurs. If the event procedure sets this argument to True, the workbook isn't saved when the procedure is finished. -- Regards, Tom Ogilvy "G Lykos" wrote in message ... Tom, thanks for your reply. Yes, intercepting the Save via the associated event seems logical. Looked at your example below and Pearson's EventSeq.xls. Having parameters for an event Sub triggered automatically upon event occurrence is unfamiliar. Cancel must be an outbound results parameter for the Sub, but what does the UI in SaveAsUI represent as a mnemonic, and where are it and Cancel defined? Is SaveAsUI a common Excel flag that the analysis macro can reset, to be picked up by your event Sub below? Thanks again, George "Tom Ogilvy" wrote in message ... You might use the beforesave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub If macros are disable, this of course will not work. If you not familiar with events see Chip Pearson's page http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "G Lykos" wrote in message ... What can be done in VBA such that <CTRL<S will be blocked from saving a spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
Unless your users are writing code, in this case it would be used to
differentiate between a Save and SaveAs. -- Regards, Tom Ogilvy "G Lykos" wrote in message ... Thanks, Chip! "Chip Pearson" wrote in message ... George, The SaveAsUI (Save As from User Interface -- the FileSave As operation) indicates whether the save was initiated by the user or by code. If by the user, SaveAsUI will be True, otherwise False. The Cancel variable allows you to cancel the save operation. Setting it to True will cancel the save. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "G Lykos" wrote in message ... Tom, thanks for your reply. Yes, intercepting the Save via the associated event seems logical. Looked at your example below and Pearson's EventSeq.xls. Having parameters for an event Sub triggered automatically upon event occurrence is unfamiliar. Cancel must be an outbound results parameter for the Sub, but what does the UI in SaveAsUI represent as a mnemonic, and where are it and Cancel defined? Is SaveAsUI a common Excel flag that the analysis macro can reset, to be picked up by your event Sub below? Thanks again, George "Tom Ogilvy" wrote in message ... You might use the beforesave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub If macros are disable, this of course will not work. If you not familiar with events see Chip Pearson's page http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "G Lykos" wrote in message ... What can be done in VBA such that <CTRL<S will be blocked from saving a spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
And to get it all out on the table - guess I would want to pop open the
SaveAs dialog box if the user opted to Save after having run the analysis, and would appreciate guidance on how to set this up. Thanks again! "G Lykos" wrote in message ... Thanks, Tom. Since the analysis Sub and the suggested event Sub both load at Excel startup (or at minimum at workbook open), then I need a mechanism for the analysis Sub to signal the event Sub to block the Save only if/when analysis has run. How/where would you suggest defining the flag? It needs to exist from Workbook open to close (is persistent the right word?), be writable by the analysis Sub and readable by the event Sub, and have a "correct" initial value (interpreted as not block a Save) without the analysis Sub having run. Thanks again, George "Tom Ogilvy" wrote in message ... SaveAsUI is an argument to the BeforeSave event that indicates whether the SaveAs Dialog will be displayed or not. Cancel is an argument to the BeforeSave event that is passed in as cancel. If you set it to True, the Save will not occur. From help on the BeforeSave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) SaveAsUi True if the Save As dialog box will be displayed. Cancel False when the event occurs. If the event procedure sets this argument to True, the workbook isn't saved when the procedure is finished. -- Regards, Tom Ogilvy "G Lykos" wrote in message ... Tom, thanks for your reply. Yes, intercepting the Save via the associated event seems logical. Looked at your example below and Pearson's EventSeq.xls. Having parameters for an event Sub triggered automatically upon event occurrence is unfamiliar. Cancel must be an outbound results parameter for the Sub, but what does the UI in SaveAsUI represent as a mnemonic, and where are it and Cancel defined? Is SaveAsUI a common Excel flag that the analysis macro can reset, to be picked up by your event Sub below? Thanks again, George "Tom Ogilvy" wrote in message ... You might use the beforesave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub If macros are disable, this of course will not work. If you not familiar with events see Chip Pearson's page http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "G Lykos" wrote in message ... What can be done in VBA such that <CTRL<S will be blocked from saving a spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
declare you variable in a general module at the top above any procedures
Public AnalysisHasRun as Boolean It would be initialized automatically upon opening as False. You would have your analysis procedure set it to true You can use the beforesave event to set cancel to True, turn off events and then handle saving with the GetSaveAsFileName procedure. Make sure you turn events back on. An alternative would be to just have the analysis procedure go the GetSaveAsFileName as its last act. Again, you might have to turn off events, then do the save, then turn them back on. -- Regards, Tom Ogilvy "G Lykos" wrote in message ... And to get it all out on the table - guess I would want to pop open the SaveAs dialog box if the user opted to Save after having run the analysis, and would appreciate guidance on how to set this up. Thanks again! "G Lykos" wrote in message ... Thanks, Tom. Since the analysis Sub and the suggested event Sub both load at Excel startup (or at minimum at workbook open), then I need a mechanism for the analysis Sub to signal the event Sub to block the Save only if/when analysis has run. How/where would you suggest defining the flag? It needs to exist from Workbook open to close (is persistent the right word?), be writable by the analysis Sub and readable by the event Sub, and have a "correct" initial value (interpreted as not block a Save) without the analysis Sub having run. Thanks again, George "Tom Ogilvy" wrote in message ... SaveAsUI is an argument to the BeforeSave event that indicates whether the SaveAs Dialog will be displayed or not. Cancel is an argument to the BeforeSave event that is passed in as cancel. If you set it to True, the Save will not occur. From help on the BeforeSave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) SaveAsUi True if the Save As dialog box will be displayed. Cancel False when the event occurs. If the event procedure sets this argument to True, the workbook isn't saved when the procedure is finished. -- Regards, Tom Ogilvy "G Lykos" wrote in message ... Tom, thanks for your reply. Yes, intercepting the Save via the associated event seems logical. Looked at your example below and Pearson's EventSeq.xls. Having parameters for an event Sub triggered automatically upon event occurrence is unfamiliar. Cancel must be an outbound results parameter for the Sub, but what does the UI in SaveAsUI represent as a mnemonic, and where are it and Cancel defined? Is SaveAsUI a common Excel flag that the analysis macro can reset, to be picked up by your event Sub below? Thanks again, George "Tom Ogilvy" wrote in message ... You might use the beforesave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub If macros are disable, this of course will not work. If you not familiar with events see Chip Pearson's page http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "G Lykos" wrote in message ... What can be done in VBA such that <CTRL<S will be blocked from saving a spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
also, have you considered making the workbook ReadOnly?
-- Regards, Tom Ogilvy "G Lykos" wrote in message ... And to get it all out on the table - guess I would want to pop open the SaveAs dialog box if the user opted to Save after having run the analysis, and would appreciate guidance on how to set this up. Thanks again! "G Lykos" wrote in message ... Thanks, Tom. Since the analysis Sub and the suggested event Sub both load at Excel startup (or at minimum at workbook open), then I need a mechanism for the analysis Sub to signal the event Sub to block the Save only if/when analysis has run. How/where would you suggest defining the flag? It needs to exist from Workbook open to close (is persistent the right word?), be writable by the analysis Sub and readable by the event Sub, and have a "correct" initial value (interpreted as not block a Save) without the analysis Sub having run. Thanks again, George "Tom Ogilvy" wrote in message ... SaveAsUI is an argument to the BeforeSave event that indicates whether the SaveAs Dialog will be displayed or not. Cancel is an argument to the BeforeSave event that is passed in as cancel. If you set it to True, the Save will not occur. From help on the BeforeSave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) SaveAsUi True if the Save As dialog box will be displayed. Cancel False when the event occurs. If the event procedure sets this argument to True, the workbook isn't saved when the procedure is finished. -- Regards, Tom Ogilvy "G Lykos" wrote in message ... Tom, thanks for your reply. Yes, intercepting the Save via the associated event seems logical. Looked at your example below and Pearson's EventSeq.xls. Having parameters for an event Sub triggered automatically upon event occurrence is unfamiliar. Cancel must be an outbound results parameter for the Sub, but what does the UI in SaveAsUI represent as a mnemonic, and where are it and Cancel defined? Is SaveAsUI a common Excel flag that the analysis macro can reset, to be picked up by your event Sub below? Thanks again, George "Tom Ogilvy" wrote in message ... You might use the beforesave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub If macros are disable, this of course will not work. If you not familiar with events see Chip Pearson's page http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "G Lykos" wrote in message ... What can be done in VBA such that <CTRL<S will be blocked from saving a spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to block file Save (^S)?
Tom, regarding making the file read-only - typically, changes are made to
the spreadsheet and not analyzed, so file saves are normal. However, when analysis is done, it manipulates the data to generate a report, and in this case, I want to block an unintended save. Thanks for your guidance with the mechanics of setting it up! Regards, George "Tom Ogilvy" wrote in message ... also, have you considered making the workbook ReadOnly? -- Regards, Tom Ogilvy "G Lykos" wrote in message ... And to get it all out on the table - guess I would want to pop open the SaveAs dialog box if the user opted to Save after having run the analysis, and would appreciate guidance on how to set this up. Thanks again! "G Lykos" wrote in message ... Thanks, Tom. Since the analysis Sub and the suggested event Sub both load at Excel startup (or at minimum at workbook open), then I need a mechanism for the analysis Sub to signal the event Sub to block the Save only if/when analysis has run. How/where would you suggest defining the flag? It needs to exist from Workbook open to close (is persistent the right word?), be writable by the analysis Sub and readable by the event Sub, and have a "correct" initial value (interpreted as not block a Save) without the analysis Sub having run. Thanks again, George "Tom Ogilvy" wrote in message ... SaveAsUI is an argument to the BeforeSave event that indicates whether the SaveAs Dialog will be displayed or not. Cancel is an argument to the BeforeSave event that is passed in as cancel. If you set it to True, the Save will not occur. From help on the BeforeSave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) SaveAsUi True if the Save As dialog box will be displayed. Cancel False when the event occurs. If the event procedure sets this argument to True, the workbook isn't saved when the procedure is finished. -- Regards, Tom Ogilvy "G Lykos" wrote in message ... Tom, thanks for your reply. Yes, intercepting the Save via the associated event seems logical. Looked at your example below and Pearson's EventSeq.xls. Having parameters for an event Sub triggered automatically upon event occurrence is unfamiliar. Cancel must be an outbound results parameter for the Sub, but what does the UI in SaveAsUI represent as a mnemonic, and where are it and Cancel defined? Is SaveAsUI a common Excel flag that the analysis macro can reset, to be picked up by your event Sub below? Thanks again, George "Tom Ogilvy" wrote in message ... You might use the beforesave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub If macros are disable, this of course will not work. If you not familiar with events see Chip Pearson's page http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "G Lykos" wrote in message ... What can be done in VBA such that <CTRL<S will be blocked from saving a spreadsheet file? Situation is that a macro has manipulated the spreadsheet for the purposes of analysis, and I would like to then block the user from inadvertently Save-ing this version of the file - but perhaps allow Save As. Thanks, George |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Block a excel file from being copied? | Excel Discussion (Misc queries) | |||
how do I block users to save the excel file if conditions not met | Excel Discussion (Misc queries) | |||
Save a cells selection as a set, group, or block | Excel Worksheet Functions | |||
Block a file in Excel from being emailed through Outlook | Excel Discussion (Misc queries) | |||
Save File to Another Directory, but not change Users File Save location | Excel Programming |