Protect worksheet not workbook

Question

I have an Excel spreadsheet that needs most of it's cells protected from editing. I can't protect the sheet in the usual way because I work with groups (little + at the top to expand certain columns).

I found a VBA macro to protect my sheet, but noticed in Excel 2010 that I could simply "unprotect sheet" and modify everything, even though the whole workbook is still protected.

This is the macro I use at "ThisWorkbook":

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="nopassword" 
If Range("C3").Value = "protect" Then 
    Range("C4:C65536").Locked = True 
Else
    Range("C4:C65536").Locked = False
End If
ActiveSheet.Protect Password:="fakepass" 
End Sub

Private Sub Workbook_Open()
Dim x As Long

For x = 1 To ActiveWorkbook.Sheets.Count
    With ActiveWorkbook.Sheets(x)
        .Protect UserInterfaceOnly:=True
        .EnableOutlining = True
    End With
Next

End Sub

How can I modify this code to work with Sheet 1?

I'm aware it's not the safest form of protection but it's merely to prevent people modifying cells accidentally.

Harley B Harley B · Accepted Answer · 2014-07-19T11:03:50

If you change:

ActiveSheet.Protect Password:="fakepass"

To:

Worksheets("Sheet1").Protect Password:="fakepass"

It will apply to Sheet1 rather than the active sheet only.

Or you could create a macro to protect all sheets, something like:

Sub ProtectAll()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="fakepass", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next ws

End Sub

And then call it into your main code?

Protect worksheet not workbook

2 Answers