SaveAs CreateBackup - remove password protection, SaveAsUI

Hi,

I have a Tracker workbook that opens another workbook (PT, as master copy), copies across some details, creates an appropriate directory and saves the PT workbook under another name (client and case number).

The PT workbook requires a password to open, has its own macro and has SaveAsUI 'disabled'. The population and general control of the PT workbook is handled by the Tracker workbook - but the user has the ability to 'stamp' the PT workbook to update the status of a process.

So far this all works fine. The trouble comes when I try and save a copy of the PT workbook to another location (different drive share, for a different audience). I want this PT Copy to be updated (overwritten) each time the workbook is saved to this secondary location. Sometimes prompted by the Tracker macro sometimes by the user directly - it doesn't make any difference.

I have used SaveCopyAs in the past but it won't work in this case as I need to remove the password to open file and prevent the user from running any macros. I've tried using SaveAs and CreateBackup but it doesn't behave how I would expect / need, and haven't been able to find out much on the interweb-googly-whatsit.

This is the code I currently have in the PT workbook:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

   
    'prevents user using SAVE AS - the Tracker must control underlying file and folder structure to function effectively - WORKS FINE
    If SaveAsUI = True Then

        MSG1 = MsgBox("You CANNOT save another version of this PT - 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

        GoTo Abort

    End If
    
    
    'if the file name contains Master (first time the PT template is opened and saved by the Tracker) skip to abort (dont save a copy to recs destination) - WORKS FINE
    CurrFileName = CStr(ThisWorkbook.Name)
    
    If InStr(1, CurrFileName, "MASTER", vbTextCompare) <> 0 Then GoTo Abort
    

    ThisWorkbook.Sheets("PT").Shapes("StampExit1").Visible = False 'hide the command button on the spreadsheet - prevents user triggering code  
  
Application.DisplayAlerts = False
    
    On Error GoTo RecsBackupErr
    
    'non password protected backup copy, file path stored in named range - DOESN'T WORK HOW I WOULD EXPECT
    ThisWorkbook.SaveAs Filename:=ThisWorkbook.Worksheets("Workings").Range("nrPTRecsPTFold") & ThisWorkbook.Name, CreateBackup:=True
       
    On Error GoTo 0

Application.DisplayAlerts = True

Abort:

    ThisWorkbook.Sheets("PT").Shapes("StampExit1").Visible = True

Exit Sub

RecsBackupErr: 'WORKS FINE
    
    MSG1 = MsgBox("An error occurred in saving a copy of the PT into the Recs 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("PT").Shapes("StampExit1").Visible = True
    
    Application.DisplayAlerts = True
    
End Sub

When this code is executed the Recs copy is saved but retains password protection and the Command Button (StampExit1) is still visible - exactly the same as the normal working version.

Am I barking up the wrong tree here? I tried going down the route of copying the sheet contents to another workbook (but sometimes there are more sheets than one) and it's a pain to change the colour pallette, formatting, delete hidden rows & columns etc, etc. It just feels so clunky and wrong.

I'm after SaveCopyAs functionality but with the password removed, the command button hidden and the 'main working copy' of the file to remain open for the user.

Is this possible? Feel like I'm missing something here...

Thanks as always, TC

Sorry, it is a bit complicated. Basically I want to save a copy of a file without a password, macro's non-triggerable to a different location and save the 'main working' version as is - Like SaveCopyAs does.

I don't want to disable events as I want it to still save the 'main working' file at the end (after BeforeSave) and leave it for the user to continue working in.

Am I missing the point?

Okay. Well it doesn't appear to get stuck in a loop when I step through it, nor when I run it normally - but I'm well aware of how little I know so added events disable and saved all within BeforeSave.

Doesn't fix my problem though - I need a SaveCopyAs but remove PW to open. What if I saved the file with no password, then did a SaveCopyAs, then saved it again with the password to open? Hmmm. Off to give it a try. Still feels a bit clunky. I'm not a fan of clunky.

So any ideas what CreateBackup:=True does? I couldn't find anything on this other than it's optional and boolean. I'm guessing my interpretation is wrong...

Okay, so that didn't work at all like I was expecting.

I did however find a workaround to achieve what I was after, it's a bit messy and requires multiple saves, but was the only way I could think to achieve what I wanted.
So here's the bit of code I'm using that:

• hides the macro command button
• saves the file without a password
• saves a copy to an alternative location
• unhides the macro command button
• saves the file with the password to open

This gives me a copy of the file in an alternative location that can be opened without a password but the macros cannot be triggeres (everything is protected and can only be altered via macro):

...

        'set filename and path variables
        CurrFileName = CStr(ThisWorkbook.Name)
        CurrFilePath = CStr(ThisWorkbook.Path)
        RecsFilePath = CStr(ThisWorkbook.Worksheets("Workings").Range("nrPTRecsPTFold").Value) 'filepath of alternative location
        
        ShortCurrFP = Right(CurrFilePath, Len(CurrFilePath) - 3) 'omits drive letter
        
        'if the file name does not contain Master (first time the PT template is opened and saved by the tracker) or
        'if the file path is not the same as the Rec's Folder (if the PT 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("PT").Shapes("StampExit1").Visible = False

Application.DisplayAlerts = False

            ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, Password:=""

            On Error GoTo RecsBackupErr

            'non password protected backup copy
            ThisWorkbook.SaveCopyAs Filename:=RecsFilePath & CurrFileName

            On Error GoTo 0

            ThisWorkbook.Sheets("PT").Shapes("StampExit1").Visible = True

            ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, Password:=ThisWorkbook.Worksheets("Workings").Range("nrProtectionPW")

Application.DisplayAlerts = True

            Cancel = True 'prevents saving again, after end / exit sub.

        End If

    End If

...

N.B. Application.EnableEvents set to false at start and true at the end

Hope this makes sense to anyone passing by.

Thanks, TC