title: Excel Macro Timestamp
body: <p>I am still having problems with the following code and not sure where to start fixing it.  When a user tries to close, I would like it to save a copy with a timestamp in the current directory where it launched from.  </p>
  
  <pre><code>Private Sub Workbook_BeforeClose(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim sFileName As String
  Dim sDateTime As String
  Dim txtFileName As String
  
  '0. Set new filename with timestamp.
  With ThisWorkbook
      sDateTime = " (" &amp; Format(Now, "yyyy-mm-dd hhmm") &amp; ").xlsm"
      sFileName = "2018 Testy" &amp; sDateTime
  
  '1. Check of Save As was used.
  If SaveAsUI = True Then
      Cancel = True
  
      '2. Call up your own dialog box.  Cancel out if user Cancels in the dialog box.
      txtFileName = Application.GetSaveAsFilename(sFileName, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
      If txtFileName = "False" Then
          MsgBox "Action Cancelled", vbOKOnly
          Cancel = True
          Exit Sub
      End If
  
      '3. Save the file.
      Application.EnableEvents = False
      ThisWorkbook.SaveAs FileName:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
      Application.EnableEvents = True
  
  End If
  End Sub
  </code></pre>