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

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" <[email protected]> wrote in message
news:%23ru%[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
news:OnE%[email protected]...
> 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" <[email protected]> wrote in message
> news:%23ru%[email protected]...
> > 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
> >
> >
>
>

George,

The SaveAsUI (Save As from User Interface -- the File>Save 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:OnE%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%23ru%[email protected]...
>> > 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
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:OnE%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:%23ru%[email protected]...
> > > 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
> > >
> > >
> >
> >
>
>

Thanks, Chip!

"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> George,
>
> The SaveAsUI (Save As from User Interface -- the File>Save 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:OnE%[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%23ru%[email protected]...
> >> > 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> Thanks, Chip!
>
> "Chip Pearson" <[email protected]> wrote in message
> news:[email protected]...
> > George,
> >
> > The SaveAsUI (Save As from User Interface -- the File>Save 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:OnE%[email protected]...
> > >> 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" <[email protected]> wrote in message
> > >> news:%23ru%[email protected]...
> > >> > 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
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:OnE%[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:%23ru%[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:OnE%[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:%23ru%[email protected]...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:OnE%[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:%23ru%[email protected]...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

also, have you considered making the workbook ReadOnly?

--
Regards,
Tom Ogilvy

"G Lykos" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:OnE%[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:%23ru%[email protected]...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> also, have you considered making the workbook ReadOnly?
>
> --
> Regards,
> Tom Ogilvy
>
> "G Lykos" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:OnE%[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > news:%23ru%[email protected]...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>