Results 1 to 10 of 10

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

  1. #1
    Board Regular
    Join Date
    Jul 2010
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Anyway to have BeforSave event runs 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.

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

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

    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
    [code]your code[/code]

  3. #3
    Board Regular
    Join Date
    Jul 2010
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by FormR View Post
    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 by drew.j.harrison; Apr 4th, 2019 at 11:42 AM.

  4. #4
    Board Regular
    Join Date
    Jul 2010
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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)
        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

  5. #5
    Board Regular
    Join Date
    Jul 2010
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by drew.j.harrison; Apr 4th, 2019 at 01:41 PM.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

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

    Cross posted https://www.excelforum.com/excel-pro...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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Jul 2010
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Fluff View Post
    Cross posted https://www.excelforum.com/excel-pro...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-pro...ml#post5096629
    Crosspost on OZGrid
    https://www.ozgrid.com/forum/forum/h...tead-of-before
    Last edited by drew.j.harrison; Apr 4th, 2019 at 01:53 PM.

  8. #8
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

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

    Quote Originally Posted by drew.j.harrison View Post
    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"?
    [code]your code[/code]

  9. #9
    Board Regular
    Join Date
    Jul 2010
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by FormR View Post
    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?

  10. #10
    Board Regular
    Join Date
    Jul 2010
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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