Simple Worksheet Protection

[James8309]

Hi everyone,

I am just trying to lock the worksheet so no one can change anything
for all the worksheets.

I have 40 worksheets in a workbook and problem I am having is that I
do know how to lock worksheets by ->tools->protection->protect sheet
but it just locks a single worksheet.

Q1. How do I lock all the other worksheet as well instead of clicking
each sheet and repeat what I just did?

Q2. Is it possible for me to set up some kind of 'track changes' and
find out where it is altered and actually who changed it?

Thank you so much for your help

Regards

James

 

[Gord Dibben]

Q1 answer.

Protect all sheets...............

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Protect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Unprotect all sheets....................

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Unprotect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Protect selected sheets..........................

Sub Protect_Selected_Sheets()
Application.ScreenUpdating = False
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="justme"
Next ws
Application.ScreenUpdating = True
End Sub

Unprotect selected sheets................

Sub UnProtect_Selected_Sheets()
Application.ScreenUpdating = False
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.UnProtect Password:="justme"
Next ws
Application.ScreenUpdating = True
End Sub

Q2.....questions

Is the workbook shared? You can track changes when it is.

Not shared? How and when would the workbook be accessed.

What changes would you want tracked.........all...........some?

It is easy enough to timestamp changes made and by whom but what constitutes
a change and would you want this recorded when someone saves the workbook?


Gord Dibben MS Excel MVP

 

[James8309]

Q1 answer.

Protect all sheets...............

Sub ProtectAllSheets()
    Application.ScreenUpdating = False
    Dim N As Single
    For N = 1 To Sheets.Count
        Sheets(N).Protect Password:="justme"
    Next N
    Application.ScreenUpdating = True
End Sub

Unprotect all sheets....................

Sub UnprotectAllSheets()
    Application.ScreenUpdating = False
    Dim N As Single
    For N = 1 To Sheets.Count
        Sheets(N).Unprotect Password:="justme"
    Next N
    Application.ScreenUpdating = True
End Sub

Protect selected sheets..........................

Sub Protect_Selected_Sheets()
Application.ScreenUpdating = False
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
 ws.Select
   ws.Protect Password:="justme"
Next ws
 Application.ScreenUpdating = True
End Sub

Unprotect selected sheets................

Sub UnProtect_Selected_Sheets()
Application.ScreenUpdating = False
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
 ws.Select
   ws.UnProtect Password:="justme"
Next ws
 Application.ScreenUpdating = True
End Sub

Q2.....questions

Is the workbook shared?  You can track changes when it is.

Not shared?  How and when would the workbook be accessed.

What changes would you want tracked.........all...........some?

It is easy enough to timestamp changes made and by whom but what constitutes
a change and would you want this recorded when someone saves the workbook?

Gord Dibben  MS Excel MVP








- Show quoted text -

Thank you for your help