VBA Save copy as read-only AND protected sheet

[mnickel]

I have a worksheet where I am attempting to have a master copy to work on and also a read-only copy for other people to view. I would also like this read-only copy to be completely protected to prevent any accidental editing in any of the cells prompting warning messages to pop-up.

I have the save as read-only copy portion of the code that works well and am wondering how I would include a protect worksheet event within this:

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

Const PathWorkListReadOnly As String = "File Path"

Application.DisplayAlerts = False

On Error Resume Next
VBA.SetAttr PathWorkListReadOnly, vbNormal
ActiveWorkbook.SaveCopyAs Filename:=PathWorkListReadOnly
VBA.SetAttr PathWorkListReadOnly, vbReadOnly

Application.DisplayAlerts = True

End Sub

Thanks!

 

[GWteB]

See if this works for you ...

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

    Const PathWorkListReadOnly As String = "File Path"

    If Not VBA.StrComp(ThisWorkbook.FullName, PathWorkListReadOnly, vbTextCompare) = 0 Then

        With Excel.Application

            .EnableEvents = False
            .DisplayAlerts = False
            .ScreenUpdating = False
            
            ThisWorkbook.Save

            On Error Resume Next
            VBA.SetAttr PathWorkListReadOnly, vbNormal
            On Error GoTo 0
            ThisWorkbook.SaveCopyAs Filename:=PathWorkListReadOnly

            Dim Wb As Workbook, Sht As Worksheet
            Set Wb = Workbooks.Open(PathWorkListReadOnly)
            For Each Sht In Wb.Worksheets
                Sht.EnableSelection = xlNoSelection
                Sht.Protect Password:="SecretPassword"
            Next Sht
            Wb.Protect Structure:=True, Windows:=True, Password:="SecretPassword"
            Wb.Save
            Wb.Close
            VBA.SetAttr PathWorkListReadOnly, vbReadOnly

            .DisplayAlerts = True
            .EnableEvents = True
            .ScreenUpdating = True
            
            ThisWorkbook.Saved = True

        End With
    End If
    Cancel = True
End Sub

 

[mnickel]

This solution worked great @GWteB, thanks so much!

I do have hyperlinks in the sheet though that need to be able to be clicked by viewers/users of the read-only/protected copy, so I left those cells unlocked in the original sheet and changed Sht.EnableSelection=xlNoSelection to =xlUnlockedCells. This kept the functionality I wanted in the read-only/protected copy.

However, there is a small annoying thing happening in the copy that I can't quite figure out. When I click on a hyperlink a selectable cell, the file/folder opens as intended but also, any time I click on a non-selectable (locked) area of the sheet afterwards, the file/folder linked to the hyperlink I previously clicked will open. When I click on a new hyperlink, the new file/folder will open as expected but then the random behavior will occur again when I click on a locked part of the sheet. This isn't a big deal, as the user wouldn't really have any reason to click anywhere else on the sheet, but just thought I'd mention it to see if anyone had any insight into this strange behavior

 

[GWteB]

Glad this one is sorted and thanks for posting back

With regard to your other issue ie the odd behaviour of your hyperlinks, I'm not able to reproduce such behaviour.
Your workbook could give some insight on that so you might consider to upload a sanitized copy to a public sharing facility like DropBox or Google Drive.
If you're willing to do so I suggest you start a new thread as this is a really different query.