• Change the default behaviour of the “save” button?

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Change the default behaviour of the “save” button?

    Author
    Topic
    #495430

    I have a spreasdsheet – when a user clicks on “Save” (or does it from the menu), I want my macro to be run instead (it will add the current date/time to the end of the filename to force a sort of versioning).

    How do I intercept the “Save” event?

    regards

    Alan

    Viewing 6 reply threads
    Author
    Replies
    • #1458444

      Alan,

      See this MS Article. Place the code in the workbook module. HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1458447

      Thanks RG. I did find that article and applied it.

      My code looks like this, but for some reason, it posts the message box twice – no idea why though – any clues?:

      It also asks me if I want to save it when I exit (but I think I can fix that with a “saved = true” setting somewhere.

      Code:
      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
              Cancel As Boolean)
      
          
          myyear = Year(Now)
          mymon = Month(Now)
          myday = Day(Now)
          SaveAs Filename:=”E:UsersAlanDocumentscubsBackup of accountsMaster ” & myyear & “-” & mymon & “-” & myday & “.xlsm”
          MsgBox (“File saved OK”)
          Cancel = true
      End Sub
      
      
    • #1458455

      Alan,

      This should fix your problem.

      Code:
      Option Explicit
      
      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
              Cancel As Boolean)
      
          Dim zMyYear  As String
          Dim zMyMon   As String
          Dim zMyDay   As String
          
          zMyYear = Year(Now)
          zMyMon = Month(Now)
          zMyDay = Day(Now)
          Application.EnableEvents = False   'Turn off Events so SaveAs doesn't recall the procedure!
          SaveAs Filename:="E:UsersAlanDocumentscubsBackup of accountsMaster " & zMyYear & "-" & zMyMon & "-" & zMyDay & ".xlsm"
          Application.EnableEvents = True    'Turn Events back on
          MsgBox ("File saved OK")
          Cancel = True
          
      End Sub
      

      You can also add: Application.DisplayAlerts = False before the save command it you don’t want it to warn you that the file already exists if you try to save it a second time on the same date!
      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1458467

        Thanks to RG for his input. If anyone wants to use this, feel free. If you wish to modify it (maybe add hours, mins seconds etc) help yourself.

        The code will check if a directory called “backup” exists underneath the original folder. It stores a copy of the file in there with todays date before saving the original one in the orginal place. Note that I have got rid of the “Option Explicit” from RG’s code as I am lazy :rolleyes:

        Cheers

        Alan

        Code:
        Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
                Cancel As Boolean)
        
            Dim zMyYear  As String
            Dim zMyMon   As String
            Dim zMyDay   As String
            Dim fs
            Set fs = CreateObject(“Scripting.FileSystemObject”) ‘ so we have access to the file system objects
            
            
            zMyYear = Year(Now)
            zMyMon = Format(Month(Now), “00”)
            zMyDay = Format(Day(Now), “00”)
            orig_Fname = ActiveWorkbook.FullName
            For i = 1 To Len(orig_Fname)
                If Mid(orig_Fname, i, 1) = “” Then jpos = i
                If Mid(orig_Fname, i, 1) = “.” Then jdot = i
            Next i
            fdir = Left(orig_Fname, jpos)
            orig_name = Mid(orig_Fname, jpos + 1, jdot – jpos – 1)
            Bdir = fdir & “backup”
            If Dir(Bdir, vbDirectory) = “” Then ‘ check that the backup directory exists
                ‘MsgBox (“No dir – ” & Bdir)
                MkDir (Bdir)
            End If
            Application.EnableEvents = False   ‘Turn off Events so SaveAs doesn’t recall the procedure!
            Application.DisplayAlerts = False
            SaveAs Filename:=Bdir & “Backup of ” & orig_name & ” ” & zMyYear & “-” & zMyMon & “-” & zMyDay & “.xlsm”
            Application.DisplayAlerts = False
            SaveAs Filename:=orig_Fname
            Application.EnableEvents = True    ‘Turn Events back on
            MsgBox (“Backup and original File saved OK”)
            Cancel = True
            
        End Sub
        
        
    • #1458459

      Cheers RG – your code works fine.

      Alan

    • #1458461

      Alan,

      ActiveWorkbook.Name

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1458474

      Alan,

      Might I suggest you move the second Application.DisplayAlerts down one line and change the False to True?

      You don’t need to specify it more than once to keep it off and you’ll want to turn it back on after you’re done since you are not exiting Excel in your code.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1458508

      Cheers – will do

    Viewing 6 reply threads
    Reply To: Change the default behaviour of the “save” button?

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: