+ Reply to Thread
Results 1 to 12 of 12

How to block file Save (^S)?

  1. #1
    G Lykos
    Guest

    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. #2
    Tom Ogilvy
    Guest

    Re: 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" <GLykos@CompuServe.com> wrote in message
    news:%23ru%23uc$1FHA.3180@TK2MSFTNGP14.phx.gbl...
    > 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. #3
    G Lykos
    Guest

    Re: 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" <twogilvy@msn.com> wrote in message
    news:OnE%23mn$1FHA.464@TK2MSFTNGP15.phx.gbl...
    > 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" <GLykos@CompuServe.com> wrote in message
    > news:%23ru%23uc$1FHA.3180@TK2MSFTNGP14.phx.gbl...
    > > 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. #4
    Chip Pearson
    Guest

    Re: How to block file Save (^S)?

    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" <GLykos@CompuServe.com> wrote in message
    news:e1LvjMB2FHA.3204@TK2MSFTNGP14.phx.gbl...
    > 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" <twogilvy@msn.com> wrote in message
    > news:OnE%23mn$1FHA.464@TK2MSFTNGP15.phx.gbl...
    >> 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" <GLykos@CompuServe.com> wrote in message
    >> news:%23ru%23uc$1FHA.3180@TK2MSFTNGP14.phx.gbl...
    >> > 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. #5
    Tom Ogilvy
    Guest

    Re: 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" <GLykos@CompuServe.com> wrote in message
    news:e1LvjMB2FHA.3204@TK2MSFTNGP14.phx.gbl...
    > 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" <twogilvy@msn.com> wrote in message
    > news:OnE%23mn$1FHA.464@TK2MSFTNGP15.phx.gbl...
    > > 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" <GLykos@CompuServe.com> wrote in message
    > > news:%23ru%23uc$1FHA.3180@TK2MSFTNGP14.phx.gbl...
    > > > 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. #6
    G Lykos
    Guest

    Re: How to block file Save (^S)?

    Thanks, Chip!

    "Chip Pearson" <chip@cpearson.com> wrote in message
    news:us2SfPB2FHA.2212@TK2MSFTNGP15.phx.gbl...
    > 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" <GLykos@CompuServe.com> wrote in message
    > news:e1LvjMB2FHA.3204@TK2MSFTNGP14.phx.gbl...
    > > 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" <twogilvy@msn.com> wrote in message
    > > news:OnE%23mn$1FHA.464@TK2MSFTNGP15.phx.gbl...
    > >> 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" <GLykos@CompuServe.com> wrote in message
    > >> news:%23ru%23uc$1FHA.3180@TK2MSFTNGP14.phx.gbl...
    > >> > 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. #7
    Tom Ogilvy
    Guest

    Re: 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" <GLykos@CompuServe.com> wrote in message
    news:%23SJEXcB2FHA.464@TK2MSFTNGP15.phx.gbl...
    > Thanks, Chip!
    >
    > "Chip Pearson" <chip@cpearson.com> wrote in message
    > news:us2SfPB2FHA.2212@TK2MSFTNGP15.phx.gbl...
    > > 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" <GLykos@CompuServe.com> wrote in message
    > > news:e1LvjMB2FHA.3204@TK2MSFTNGP14.phx.gbl...
    > > > 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" <twogilvy@msn.com> wrote in message
    > > > news:OnE%23mn$1FHA.464@TK2MSFTNGP15.phx.gbl...
    > > >> 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" <GLykos@CompuServe.com> wrote in message
    > > >> news:%23ru%23uc$1FHA.3180@TK2MSFTNGP14.phx.gbl...
    > > >> > 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. #8
    G Lykos
    Guest

    Re: 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" <twogilvy@msn.com> wrote in message
    news:O$SZ5RB2FHA.268@TK2MSFTNGP09.phx.gbl...
    > 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" <GLykos@CompuServe.com> wrote in message
    > news:e1LvjMB2FHA.3204@TK2MSFTNGP14.phx.gbl...
    > > 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" <twogilvy@msn.com> wrote in message
    > > news:OnE%23mn$1FHA.464@TK2MSFTNGP15.phx.gbl...
    > > > 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" <GLykos@CompuServe.com> wrote in message
    > > > news:%23ru%23uc$1FHA.3180@TK2MSFTNGP14.phx.gbl...
    > > > > 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. #9
    G Lykos
    Guest

    Re: 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" <GLykos@CompuServe.com> wrote in message
    news:%23N0QzlB2FHA.3660@TK2MSFTNGP15.phx.gbl...
    > 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" <twogilvy@msn.com> wrote in message
    > news:O$SZ5RB2FHA.268@TK2MSFTNGP09.phx.gbl...
    > > 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" <GLykos@CompuServe.com> wrote in message
    > > news:e1LvjMB2FHA.3204@TK2MSFTNGP14.phx.gbl...
    > > > 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" <twogilvy@msn.com> wrote in message
    > > > news:OnE%23mn$1FHA.464@TK2MSFTNGP15.phx.gbl...
    > > > > 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" <GLykos@CompuServe.com> wrote in message
    > > > > news:%23ru%23uc$1FHA.3180@TK2MSFTNGP14.phx.gbl...
    > > > > > 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. #10
    Tom Ogilvy
    Guest

    Re: 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" <GLykos@CompuServe.com> wrote in message
    news:O4fBzoB2FHA.1168@TK2MSFTNGP10.phx.gbl...
    > 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" <GLykos@CompuServe.com> wrote in message
    > news:%23N0QzlB2FHA.3660@TK2MSFTNGP15.phx.gbl...
    > > 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" <twogilvy@msn.com> wrote in message
    > > news:O$SZ5RB2FHA.268@TK2MSFTNGP09.phx.gbl...
    > > > 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" <GLykos@CompuServe.com> wrote in message
    > > > news:e1LvjMB2FHA.3204@TK2MSFTNGP14.phx.gbl...
    > > > > 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" <twogilvy@msn.com> wrote in message
    > > > > news:OnE%23mn$1FHA.464@TK2MSFTNGP15.phx.gbl...
    > > > > > 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" <GLykos@CompuServe.com> wrote in message
    > > > > > news:%23ru%23uc$1FHA.3180@TK2MSFTNGP14.phx.gbl...
    > > > > > > 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. #11
    Tom Ogilvy
    Guest

    Re: How to block file Save (^S)?

    also, have you considered making the workbook ReadOnly?

    --
    Regards,
    Tom Ogilvy

    "G Lykos" <GLykos@CompuServe.com> wrote in message
    news:O4fBzoB2FHA.1168@TK2MSFTNGP10.phx.gbl...
    > 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" <GLykos@CompuServe.com> wrote in message
    > news:%23N0QzlB2FHA.3660@TK2MSFTNGP15.phx.gbl...
    > > 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" <twogilvy@msn.com> wrote in message
    > > news:O$SZ5RB2FHA.268@TK2MSFTNGP09.phx.gbl...
    > > > 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" <GLykos@CompuServe.com> wrote in message
    > > > news:e1LvjMB2FHA.3204@TK2MSFTNGP14.phx.gbl...
    > > > > 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" <twogilvy@msn.com> wrote in message
    > > > > news:OnE%23mn$1FHA.464@TK2MSFTNGP15.phx.gbl...
    > > > > > 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" <GLykos@CompuServe.com> wrote in message
    > > > > > news:%23ru%23uc$1FHA.3180@TK2MSFTNGP14.phx.gbl...
    > > > > > > 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. #12
    G Lykos
    Guest

    Re: 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" <twogilvy@msn.com> wrote in message
    news:%23deVRyB2FHA.3180@TK2MSFTNGP10.phx.gbl...
    > also, have you considered making the workbook ReadOnly?
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "G Lykos" <GLykos@CompuServe.com> wrote in message
    > news:O4fBzoB2FHA.1168@TK2MSFTNGP10.phx.gbl...
    > > 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" <GLykos@CompuServe.com> wrote in message
    > > news:%23N0QzlB2FHA.3660@TK2MSFTNGP15.phx.gbl...
    > > > 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" <twogilvy@msn.com> wrote in message
    > > > news:O$SZ5RB2FHA.268@TK2MSFTNGP09.phx.gbl...
    > > > > 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" <GLykos@CompuServe.com> wrote in message
    > > > > news:e1LvjMB2FHA.3204@TK2MSFTNGP14.phx.gbl...
    > > > > > 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" <twogilvy@msn.com> wrote in message
    > > > > > news:OnE%23mn$1FHA.464@TK2MSFTNGP15.phx.gbl...
    > > > > > > 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" <GLykos@CompuServe.com> wrote in message
    > > > > > > news:%23ru%23uc$1FHA.3180@TK2MSFTNGP14.phx.gbl...
    > > > > > > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1