Hi,
Bit of a strange one this.
I have two workbooks, one a Tracker which houses the majority of data and code and the other a Timeline which is largely controlled by the Tracker but also has some small amount of code.
The Timeline has some code in the BeforeSave event which prevents the user from using SaveAs, backs the file up to an alternate location, etc. When I click on save whilst in the time line the code runs perfectly. However when I initially set up the Timeline using code housed in the tracker the save fails - with no error.
The .save line in the Tracker triggers the Timeline's BeforeSave but when it goes over each line of code to save and create the backup, nothing happens. I've tried running it with DisplayAlerts and EnableEvents set to true - just to see if any error message shows - but nothing. Perplexed.
Tracker:
Sub PopTimeline
'Open Timeline, Set TLWB as workbook, do stuff
TLWB.Save
End Sub
Timeline:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
'prevents user using SAVE AS - the Tracker must control underlying file and folder structure to function effectively
If SaveAsUI = True Then
MSG1 = MsgBox("You CANNOT save another version of this Timeline - you can only save changes to the existing version." & vbNewLine _
& vbNewLine & "Please use 'SAVE' and NOT 'SAVE AS'", vbCritical, "Save As NOT allowed")
Cancel = True
Else
'set filename and path variables
CurrFileName = CStr(ThisWorkbook.Name)
CurrFilePath = CStr(ThisWorkbook.Path)
RecsFilePath = CStr(ThisWorkbook.Worksheets("Workings").Range("nrPTRecsPTFold").Value)
ShortCurrFP = Right(CurrFilePath, Len(CurrFilePath) - 3) 'omits drive letter
'if the file name does not contain Master (first time the Timeline template is opened and saved by the Tracker) or
'if the file path is not the same as the Rec's Folder (if the TL is opened and saved from this location)
'then save a copy to recs folder
If InStr(1, CurrFileName, "MASTER", vbTextCompare) = 0 And InStr(1, RecsFilePath, ShortCurrFP, vbTextCompare) = 0 Then
ThisWorkbook.Sheets("Timeline").Shapes("StampExit1").Visible = False
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, Password:="" 'DOES NOT TRIGGER
On Error GoTo RecsBackupErr
'non password protected backup copy
ThisWorkbook.SaveCopyAs Filename:=RecsFilePath & CurrFileName 'DOES NOT TRIGGER
On Error GoTo 0
ThisWorkbook.Sheets("Timeline").Shapes("StampExit1").Visible = True
ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, Password:=ThisWorkbook.Worksheets("Workings").Range("nrProtectionPW")
'DOES NOT TRIGGER
Application.DisplayAlerts = True
Cancel = True 'prevents saving again, after end / exit sub.
End If
End If
Application.EnableEvents = True
Exit Sub
RecsBackupErr:
MSG1 = MsgBox("An error occurred in saving a copy of the Timeline into the Recs back up folder, the file may not have been saved. " _
& vbNewLine & vbNewLine & "Please check and try again - saving the file will automatically back up to the Recs folder", vbCritical, "File Save Error")
ThisWorkbook.Sheets("Timeline").Shapes("StampExit1").Visible = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Any ideas why the BeforeSave code would run fine when triggered by the user (e.g. File > Save) but not when triggered from another Macro?
Any help appreciated - as always, TC.
Bookmarks