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

[drew.j.harrison]

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]

Hi, you could try something like this in the ThisWorkbook module.

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]

Hi, you could try something like this in the ThisWorkbook module.

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?

 

[drew.j.harrison]

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?

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]

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.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...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]

Cross posted https://www.excelforum.com/excel-pr...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-pr...ion-prompt-instead-of-before.html#post5096629
Crosspost on OZGrid
https://www.ozgrid.com/forum/forum/...fter-saveas-location-prompt-instead-of-before

 

[FormR]

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]

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]

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!

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