Protecting whole workbook

[Tanya]

Hi,
I have a workbook with approx 30 worksheets and have protected each
worksheet with the following macro's

Private Sub Protect_Workbook_Click()
'Protect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = False Then
ws.Protect ("BBHS")
End If
Next
ActiveWorkbook.Protect (["BBHS"])
Application.ScreenUpdating = True


End Sub

Private Sub UnProtect_Workbook_Click()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True


End Sub

What I am finding is after I run the protection macro is each worksheet
allows you still to click in every cell. My question is, how do get the
macro to protect the worksheets in such a way that you can only click on a
cell that is unprotected?
If anyone could help with this I would be appreciate it greatly.
Regards
Tanya

 

[Mike H]

Very minor change to your code will do the trick:-

Private Sub Protect_Workbook_Click()
'Protect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = False Then
ws.Protect ("BBHS")
ActiveSheet.EnableSelection = xlUnlockedCells
End If
Next
ActiveWorkbook.Protect (["BBHS"])
Application.ScreenUpdating = True

Mike


End Sub

 

[Norman Jones]

Hi Tanya,

See the EnableSelection property in VBA help.

However, this property is not persistant, Therefore, try protecting the
sheets in the Workbook_Open procedure:

'<<=============
Private Sub Protect_Workbook_Click()
Dim ws As Worksheet
Const PWORD As String = "Pippo"

For Each ws In Me.Worksheets
With ws
If .ProtectContents = False Then
.EnableSelection = xlUnlockedCells
.Protect password:=PWORD
End If
End With
Next ws

ActiveWorkbook.Protect password:=PWORD
End Sub
'<<=============

This is workbook event code and should be pasted into
the workbook's ThisWorkbook module *not* a standard
module or a sheet module:

Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is
maximised)
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

 

[Norman Jones]

Hi Tanya,

The code was intended as:

'<<=============
Private Sub Protect_Workbook_Open() '<<====
Dim ws As Worksheet
Const PWORD As String = ""BBHS" '<<====

For Each ws In Me.Worksheets
With ws
If .ProtectContents = False Then
.EnableSelection = xlUnlockedCells
.Protect password:=PWORD
End If
End With
Next ws

ActiveWorkbook.Protect password:=PWORD
End Sub
'<<=============

 

[Tanya]

Thank you Norman!
It worked a charm.
Kind Regards
Tanya

Private Sub Protect_Workbook_Click() '<==== I am using a command button to
run this macro
'Protect workbook
Dim ws As Worksheet
Const PWORD As String = "BBHS"
For Each ws In ActiveWorkbook.Worksheets '<==== I replaced me with
ActiveWorkbook.
With ws
If ws.ProtectContents = False Then
.EnableSelection = xlUnlockedCells
.Protect Password:=PWORD
End If
End With
Next ws
ActiveWorkbook.Protect Password:=PWORD

End Sub

 

[Tanya]

Thank you for your response Mike
Your solution didn't quite work as well as expected.
Kind Regards

Very minor change to your code will do the trick:-

Private Sub Protect_Workbook_Click()
'Protect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = False Then
ws.Protect ("BBHS")
ActiveSheet.EnableSelection = xlUnlockedCells
End If
Next
ActiveWorkbook.Protect (["BBHS"])
Application.ScreenUpdating = True

Mike


End Sub

Hi,
I have a workbook with approx 30 worksheets and have protected each
worksheet with the following macro's

Private Sub Protect_Workbook_Click()
'Protect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = False Then
ws.Protect ("BBHS")
End If
Next
ActiveWorkbook.Protect (["BBHS"])
Application.ScreenUpdating = True


End Sub

Private Sub UnProtect_Workbook_Click()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True


End Sub

What I am finding is after I run the protection macro is each worksheet
allows you still to click in every cell. My question is, how do get the
macro to protect the worksheets in such a way that you can only click on a
cell that is unprotected?
If anyone could help with this I would be appreciate it greatly.
Regards
Tanya