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.
Any way to have BeforeSave event run after SaveAs location prompt instead of before?
-
drew.j.harrison -
April 4, 2019 at 6:58 AM -
Thread is marked as Resolved.
-
-
-
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
-
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.
Code
Display MorePrivate Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) [COLOR=#0000FF]MsgBox "Before save event triggered"[/COLOR] [COLOR=#008000] 'Check if it is a Save As action[/COLOR] If SaveAsUI Then [COLOR=#008000]'Set UsingSaveAs to True and exit macro[/COLOR] UsingSaveAs = True [COLOR=#0000FF]MsgBox "UsingSaveAs flag set to true"[/COLOR] Exit Sub Else [COLOR=#008000] 'Disable trigger events[/COLOR] Application.EnableEvents = False [COLOR=#008000]'Save workbook[/COLOR] ThisWorkbook.Save [COLOR=#008000] 'ReEnable trigger events[/COLOR] Application.EnableEvents = True End If End Sub Private Sub Workbook_AfterSave(ByVal Success As Boolean) [COLOR=#0000FF]MsgBox "After save event triggered"[/COLOR] [COLOR=#008000]'Check if Save As was used[/COLOR] If UsingSaveAs And Success Then [COLOR=#0000FF]MsgBox "Save As complete event triggered"[/COLOR] [COLOR=#008000]'INSERT CODE FOR AFTER SAVE AS EVENT[/COLOR] [COLOR=#008000] 'Set UsingSaveAs back to False[/COLOR] UsingSaveAs = False [COLOR=#0000FF]MsgBox "UsingSaveAs flag set back to false"[/COLOR] End If End Sub
-
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 ...
-
-
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 ?
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.
-
FYI this is crossposted in the following areas:
Crosspost on MrExcel
https://www.mrexcel.com/forum/excel-qu…tml#post5255351Crosspot on ExcelForum
https://www.excelforum.com/excel-programm…tml#post5096629 -
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 ... ???
-
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!
Code
Display MorePrivate UsingSaveAs As Boolean Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MsgBox "Before save event triggered" 'Check if it is a Save As action If SaveAsUI Then 'Set UsingSaveAs to True and exit macro UsingSaveAs = True MsgBox "UsingSaveAs flag set to true" Exit Sub Else 'Disable trigger events Application.EnableEvents = False 'Save workbook ThisWorkbook.Save 'ReEnable trigger events Application.EnableEvents = True End If End Sub Private Sub Workbook_AfterSave(ByVal Success As Boolean) MsgBox "After save event triggered" 'Check if Save As was used If UsingSaveAs And Success Then MsgBox "Save As complete event triggered" 'INSERT CODE FOR AFTER SAVE AS EVENT 'Set UsingSaveAs back to False UsingSaveAs = False MsgBox "UsingSaveAs flag set back to false" End If End Sub
-
-
Glad you could fix your problem ...:smile:
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!