Prevent Saving Certain Worksheets On Save As

RickWHaas · Jan 18th 2008

In my program I have prevented the user from saving the Workbook with the following code

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim p As String
    Cancel = True
    Do While Cancel = True
        p = InputBox("Enter  password to  save file:", "Password Required To Save", "")
        If p = "Enter Password Here" Then
            Cancel = False
        Else
            yn = MsgBox("Wrong password. Try again?", vbYesNo, "Invalid Password")
            If yn = vbNo Then
                Exit Sub
            End If
        End If
     Loop
End Sub

Display More

I also need to prevent the user, when they use the save as, from saving certain worksheets.

Ex: There are 6 worksheets in the workbook. I only want them to be able to save the first three.

Issues: One of the workbooks that I want to allow them to change has a tab name that changes frequently.

Thanks

pdsasse · Jan 18th 2008

Re: Prevent Saving Certain Worksheets On Save As

Something like this???

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim p As String
    Dim Wb As Workbook
    Dim SaveAsFile As String
    Cancel = True
    Do While Cancel = True
        p = InputBox("Enter   password to  save file:", "Password Required To Save", "")
        If p = "bob" Then
            Sheets(Array("Sheet6", "Sheet5", "Sheet4")).Copy
            Set Wb = Workbooks(Workbooks.Count)
10
            SaveAsFile = Application.GetSaveAsFilename
            If SaveAsFile = "False" Then GoTo FalseSave
Application.EnableEvents = False
Application.DisplayAlerts = False
            Wb.SaveAs SaveAsFile
Application.DisplayAlerts = True
Application.EnableEvents = True
Exit Sub
            Cancel = False
        Else
            yn = MsgBox("Wrong password. Try again?", vbYesNo, "Invalid Password")
            If yn = vbNo Then
                Exit Sub
            End If
        End If
     Loop
Exit Sub
FalseSave:
Application.DisplayAlerts = False
            Wb.Close
Application.DisplayAlerts = True
End Sub

Display More

Instead of going on with the normal SaveAs procedure, it copies only the listed tabs into a new WB and saves it to the location the user selects...

The process should be pretty transparent to the user....

Dave Hawley · Jan 19th 2008

Re: Prevent Saving Certain Worksheets On Save As

As shown, the only way is to move the Worksheets to another Workbook. You cannot save by Sheet in Workbook.

It sounds like you need 2 files where one is read only.

Prevent Saving Certain Worksheets On Save As

Participate now!

Share