Protect - Work Sheet / Book

Hi Anand

You can srt protection to a spreadsheet via VBA by using the syntax:
Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly)

So some code may look like:

Sheet1.Protect Password:="secret",UserInterfaceOnly:=True

Protecting a Sheet or Sheets this way will NOT let the user make any changes, but WILL let VBA code make changes. However there are some flaws in this:

If you apply the Protect method with the UserInterfaceOnly argument set to True to a worksheet and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To unprotect the worksheet but re-enable user interface protection after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.

The sheet can also become unprotected using certain VBA routines such as FillAcrossSheets and a few others, it is for this reason I suggest using this code in a normal module:

Dim Wsht As Worksheet
'Written by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''
'Will Protect/Unprotect ALL worsheets
'''''''''''''''''''''''''''''''''''''''''

Sub ProtectAllSheets()
On Error Resume Next
For Each Wsht In ThisWorkbook.Worksheets
Wsht.Protect Password:="secret"
Next Wsht

Sub UnProtectAllSheets()
On Error Resume Next
For Each Wsht In ThisWorkbook.Worksheets
Wsht.Unprotect Password:="secret"
Next Wsht
End Sub

Then start all subs that need Sheets unprotected with:

Sub Whatever()
on error goto LockUp
Run "UnProtectAllSheets"
'Your Code......
'.................
'.................
LockUp:
Run "UnProtectAllSheets"
End sub

Dave

OzGrid Business Applications

Then start all subs that need Sheets unprotected with: Sub Whatever() on error goto LockUp Run "UnProtectAllSheets" 'Your Code...... '................. '................. LockUp: Run "UnProtectAllSheets" End sub

Dave

Regarding you Protection message, you could use the Sheet Selection Change Event fo this:

Right click on the Sheet name tab and select "View Code", paste in this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Written by OzGrid Business Applications
'www.ozgrid.com

'''''''''''''''''''''''''''''''''''''''
'Stop the default protection message
''''''''''''''''''''''''''''''''''''''''

Dim RwatchRange As Range
On Error GoTo ResetEvent

If Target.Cells.Count > 1 Then Exit Sub
If Target.Locked = False Then Exit Sub

Set RwatchRange = Range("A1:A10")

If Not Intersect(Target, RwatchRange) Is Nothing Then

MsgBox "Sorry, you cannot go here!", vbCritical, "Secrurity"
Application.EnableEvents = False
Range("B1").Select
End If

Set RwatchRange = Nothing
ResetEvent:
Application.EnableEvents = True
End Sub

It will prevent the user from selecting any cells in the range A1:A10 IF the cells are locked.

Dave

OzGrid Business Applications

Hi Dave,

Thanks a lot for your quick and apt response
It works.

Regards

Anand