Results 1 to 7 of 7

Thread: How to run FileSave Macro when Save is selected in VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2016
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to run FileSave Macro when Save is selected in VBA

    I have the following Macro that saves a file to a different location with the file name being what is in cell B7.

    Code:
    Sub FileSave ()
    Dim FP As String, FN As String
    FP = ":\Quotes\"
    FN = Range("B7").Value
    ActiveWorkbook.SaveAs filename:= FP & FN & ".xls"
    
    End Sub
    How do I get this to RUN whenever any method of Save is selected.

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to run FileSave Macro when Save is selected in VBA

    In the VBA editor, select the 'ThisWorkbook' object and add code to the Workbook_BeforeSave event.

    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim FP As String, FN As String
    FP = ":\Quotes\"
    FN = Range("B7").Value
    ActiveWorkbook.SaveAs Filename:=FP & FN & ".xls"
    
    End Sub
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    New Member
    Join Date
    Oct 2016
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to run FileSave Macro when Save is selected in VBA

    I have tried this but fails to run - stops on
    ActiveWorkbook.SaveAs Filename:=FP & FN & ".xls"

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,454
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: How to run FileSave Macro when Save is selected in VBA

    Does the file you want to save to a different location have a .xls file extension? If not, what is its file extension?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    New Member
    Join Date
    Oct 2016
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to run FileSave Macro when Save is selected in VBA

    Any File with VBA in the Workbook has to be a Macro Enabled File so it has a file extension of .xlsm. Have tried with .xlsm and with no file extension - still fails to run.

  6. #6
    New Member
    Join Date
    Oct 2016
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to run FileSave Macro when Save is selected in VBA

    Thank you both for your time. Problem has been resolved. IT man hadn't shared the E:\Quotes| folder. Your assistance with the Before Save worked. Once again Thank you.

  7. #7
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to run FileSave Macro when Save is selected in VBA

    ───────────────────░█▓▓▓█░▇▆▅▄▃▂
    ──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
    ─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
    ──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
    ─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
    ───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
    ──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
    ────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
    ───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
    ──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
    ──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
    ──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
    ─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
    ─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
    ─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
    ░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
    ░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
    ░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
    ░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
    ─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
    ─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
    ──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
    ───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
    ────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
    ──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
    ──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

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
  •