Any way to have BeforeSave event run after SaveAs location prompt instead of before?

  • So I have some code that I don't want to run until after the user selects save on the SaveAs save location prompt but it appears the BeforeSave event runs the code before the prompt even opens. Is there anyway around this or some other method to define a trigger event for once the user selects save in the SaveAs location prompt? Thanks.

  • Hello,


    The short answer is NO ...


    The sequence of execution of all the Worksheet Events is hard coded within Excel ...


    Most probably, if you were to think over your various instructions, there is a way to shift them around in order to achieve what you need ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I am currently trying to work around the issue by setting a flag to True that SaveAs is being used and exiting the BeforeSave event if the SaveAsUI is True then in the AfterSave event referencing the SaveAs flag to determine whether to run additional code or not. However, this does not appear to work as apparently the AfterSave event is also only triggered during a normal save event and not a SaveAs event completion. Do you happen to know if there is any way to trigger an event after a SaveAs event is completed?

  • Below is an example of how I was trying to accomplish this. Obviously not working now since the AfterSave does not appear to be triggered after completing a Save As event.


  • Hello again,


    Probably UsingSaveAs is a Boolean variable ... set by another procedure ...


    Thanks for your confirmation ...


    Regarding your objective ... could you merge the two events into the BeforeSave ?


    Do not hesitate to explain in plain English what you are exactly after ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)


  • I am not setting the UsingSaveAs to Boolean anywhere else. Is this necessary? Thought it just defaulted to that based on some other code I have that appears to work that way.


    I can't seem to think of anyway to merge all of it into before save off hand. Below is a breakdown of exactly what I am trying to accomplish.


    Scenario 1
    User hits save
    BeforeSave_Event hides all worksheets and unhides "Enable Macros" worksheet then saves (already have working code)
    If file is not being closed then it will rehide the "Enable Macros" and unhide all the previously unhidden worksheets (already have working code)


    Scenario 2
    User hits Save As
    User hits "Browse"
    BeforeSave_Event hides all worksheets and unhides "Enable Macros" worksheet but does not save (already have working code)
    User selects save location, file name and hits save or cancel
    After completion of Save As or Canel a macro will trigger to rehide the "Enable Macros" and unhide all the previously unhidden worksheets (don't have a way to trigger this so right now after a Save As event the file stays hidden with the "Enable Macros" worksheet showing)


    I could put a very rudimentary work around of a button that can be pressed on the "Enable Macros" worksheet that will unhide the other worksheets again but would prefer for this action to be triggered automatically in some way as it currently is on the normal save action. Appears that the AfterSave event won't work unless I am missing something.

  • A quick thought ...


    What about disabling both Save and Save As ...


    and create the customized ' Save ' macro which would execute the exact sequence of actions you do require ... ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I suppose I could do that but trying to keep the base functionality of save and save as in excel rather than code explicit replacements to prevent any issues. Looks like for now I'm using a button on the enable macro sheet until I can find a better solution. I've attached the current in progress file for reference if anyone wants to see how I am handling it with the button. Likely will post a general excel template for anyone to use as a base template with selectable functionality once I finish up a couple more features I am working on.

  • After some more prodding around, it appears the following base code works to run on SaveAs events! Thanks all for the help!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!