Consulting

Results 1 to 5 of 5

Thread: Run Macro Save AS

  1. #1
    VBAX Newbie
    Joined
    Aug 2014
    Posts
    3
    Location

    Run Macro Save AS

    Hello,
    need some help

    I need the worksheet is saved with the name that is in cell A1
    The following macro can accomplish what I need, but I need to call this macro automatically when I save by doing the following path File -> Save As ...
    When I click File -> Save As, the macro runs, opens a dialog box with the text in cell A1, (is what I need), but after save, opens another dialog box to save again.
    I want to save only 1x.

    This Workbook

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call SaveAS
    End Sub
    Module

    Sub SaveAS()
    Dim fname
    ChDrive (Left(ThisWorkbook.Path, 1))
    ChDir ThisWorkbook.Path
    reference = Range("A1").Value & " " & "-" & " " & Format(Now, "dd-mm-yyyy-hhmmss")
    fname = Application.GetSaveAsFilename(reference, FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Save As")
    If fname = False Then Exit Sub
    ActiveWorkbook.SaveAs Filename:=fname
    End Sub
    Last edited by Aussiebear; 08-05-2014 at 03:34 PM. Reason: Added code tags

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Add Cancel = TRUE to the event procedure.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Newbie
    Joined
    Aug 2014
    Posts
    3
    Location
    Quote Originally Posted by xld View Post
    Add Cancel = TRUE to the event procedure.
    not solved
    still opens 2 times this dialog and now the file is not saved

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    I forgot an important point, turn events off (I had to rename SaveAS to SaveASx)

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        On Error GoTo wb_exit
        Application.EnableEvents = False
        Cancel = True
        Call SaveASx
    wb_exit:
        Application.EnableEvents = True
    End Sub
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Newbie
    Joined
    Aug 2014
    Posts
    3
    Location
    Quote Originally Posted by xld View Post
    I forgot an important point, turn events off (I had to rename SaveAS to SaveASx)

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        On Error GoTo wb_exit
        Application.EnableEvents = False
        Cancel = True
        Call SaveASx
    wb_exit:
        Application.EnableEvents = True
    End Sub
    Now it worked

    xld, Thank you so much

Posting Permissions

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