Hi Tanya,

See the EnableSelection property in VBA help.

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

'<<=============
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.


---
Regards,
Norman


"Tanya" wrote in message
...
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