Anyway to have BeforSave event runs after SaveAs location prompt instead of before?

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
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.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,216
Office Version
365
Platform
Windows
Hi, you could try something like this in the ThisWorkbook module.

Code:
Dim wbName As String


Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If wbName <> Me.FullName And Success Then
    'Your code here for example..
    MsgBox "The user has saved as"
End If
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
wbName = Me.FullName
End Sub
 

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
Hi, you could try something like this in the ThisWorkbook module.

Code:
Dim wbName As String


Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If wbName <> Me.FullName And Success Then
    'Your code here for example..
    MsgBox "The user has saved as"
End If
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
wbName = Me.FullName
End Sub

Looks like this could work but I noticed that the "Workbook_AfterSave" does not trigger after a SaveAs completion only after a Save event completion. Anyway to change this?
 
Last edited:

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
Below is an example of how I was trying to accomplish this by setting a UsingSaveAs flag during the BeforeSave event and the referencing its status in the AfterSave event. This is similar to what you posted above it just doesn't recognize whether the file was actually saved unlike yours, it only recognizes whether the SaveAsUI was opened. This is actually necessary in my case since I am running code before it checks for SaveAsUI and then will need to undo the changes the code made regardless of whether the user saves or not in the Save As UI . However, this is not working since the AfterSave does not appear to be triggered after completing a Save As event only after a normal Save event. Any thoughts on how I can trigger an event/macro after Save As is completed?


Code:
Private 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
        'Set UsingSaveAs back to False[/COLOR]
        UsingSaveAs = False
        [COLOR="#0000FF"]MsgBox "UsingSaveAs flag set back to false"[/COLOR]
    End If
End Sub
 

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
Below are the 2 actual scenarios I am trying to accomplish if that is of any assitance in understanding my goal here.

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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,206
Office Version
365
Platform
Windows
Cross posted https://www.excelforum.com/excel-programming-vba-macros/1271151-any-way-to-have-beforesave-event-run-after-saveas-location-prompt-instead-of-before.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
Cross posted https://www.excelforum.com/excel-programming-vba-macros/1271151-any-way-to-have-beforesave-event-run-after-saveas-location-prompt-instead-of-before.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
Thanks for the notice. It is aso crossposted on OZgrid. Links are below

Crosspost on ExcelForums
https://www.excelforum.com/excel-programming-vba-macros/1271151-any-way-to-have-beforesave-event-run-after-saveas-location-prompt-instead-of-before.html#post5096629
Crosspost on OZGrid
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1216948-any-way-to-have-beforesave-event-run-after-saveas-location-prompt-instead-of-before
 
Last edited:

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,216
Office Version
365
Platform
Windows
However, this is not working since the AfterSave does not appear to be triggered after completing a Save As event only after a normal Save event.
It triggers for me after a save of any type - how and where have you declared the variable "UsingSaveAs"?
 

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
It triggers for me after a save of any type - how and where have you declared the variable "UsingSaveAs"?
Ah yes, I forgot to declare UsingSaveAs as Boolean. I did just add this however, I found that after a save as event the AfterSave is never triggered to begin with. I never get the MsgBox "After save event triggered" let alone getting to the line where it checks if UsingSaveAs is set to True. Are you getting the MsgBox "After save event triggered" after a Save As is completed?
 

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
Nevermind, it appears to be working now with the base code. Not sure what I had wrong in the other file with the actual code. I think maybe the events are still disabled when it would normally in aftersave. Below is the working code. Thanks again!

Code:
Private 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
 

Forum statistics

Threads
1,081,983
Messages
5,362,550
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top