MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Protect - Work Sheet / Book


Posted by Anand on April 25, 2001 5:03 AM

Hi ,

can someone please help with this. I have protected a worksheet and when someone tries to enter the values it fires with the default message , how can i replace this with my application error messages. And can i do it based on the cell selected . How can i protect my macro code from the end user but have macros running. Workbook protect didnt allow me to run the macros.

Any Help will be appreciated.

Best Regards

Anand


Posted by Dave Hawley on April 25, 2001 5:21 AM


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

Posted by Dave Hawley on April 25, 2001 5:31 AM

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

Posted by Anand on April 25, 2001 5:54 AM

Hi Dave,

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

Regards

Anand