Hi,
The code below works perfectly when using the save as button.
When using the save button, the code seems to error and the enableevents = true, is not working?
Can anybody point me in the right direction?
Thanks
Code
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
If SaveAsUI = False Then
Cancel = True
MsgBox ("Not Saved. Please use SaveAs button & choose Monthly Folder")
End If
Application.EnableEvents = False
If SaveAsUI = True Then
Dim wb As Workbook
Dim NewFileName As String
Dim NewFileFilter As String
Dim myTitle As String
Dim FileSaveName As Variant
Dim NewFileFormat As Long
Set wb = ThisWorkbook
NewFileName = wb.Sheets("Timesheet").Range("a1").Value & ".xlsm"
NewFileFilter = "Microsoft Excel Macro-Enabled workbook (*.xlsm), *.xlsm"
NewFileFormat = xlNormal
myTitle = "Navigate to the required folder"
FileSaveName = Application.GetSaveAsFilename _
(InitialFileName:=NewFileName, _
FileFilter:=NewFileFilter, _
Title:=myTitle)
If Not FileSaveName = False Then
wb.SaveAs Filename:=FileSaveName, _
FileFormat:=NewFileFormat
Else
MsgBox "File NOT Saved. Please use 'Save As' and save to Monthly Folder"
End If
Application.EnableEvents = True
Exit Sub
End If
End Sub
Display More