Protect cells

[JACOB]

Is there a way to protect cells that has programing or funcions, WITHOUT
locking the sheet. To lock the sheet is very bothersome that prevents me
from unhiding and formating etc.

 

[EZ Money]

No. Protection is only invoked when the book or ws is protected.

 

[Dave Peterson]

Another way that is close to Paul's suggestion...

Give your range of cells that should be "protected" a nice name. (I used
Protected.)

Then right click on the worksheet tab that should have this behavior and select
View Code. Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("Protected")) Is Nothing Then
Exit Sub
End If

On Error GoTo errHandler:
With Application
.EnableEvents = False
.Undo
End With

errHandler:
Application.EnableEvents = True

End Sub


But both this suggestion and Paul's suffer from the same problem(s). If the
user opens the workbook with macros disabled (or just turns off event handling),
then it fails miserably.

===
Excel 2002 does have finer granularity on what you can allow on a protected
worksheet. And if you provided a macro that does the real work, you could let
the workbook allow the macro to do more than the user.

If you protect the worksheet in code, you can allow more stuff to happen. But
this setting is not persistent between workbook closings. You have to reset
each time you need it. Using workbook_open (or auto_open) makes it nice.

Option Explicit
Private Sub Workbook_Open()
Worksheets("sheet1").Protect Password:="hi", userinterfaceonly:=True
End Sub

===
And you could even just have your macro unprotect the worksheet, do the changes,
and reprotect the worksheet.

 

[ABC]

You mentioned you could unprotect then reportect. I think this would
be the best for me. How do you do this?

Thanks

ABC

 

[Dave Peterson]

If you record a macro, you'll see the syntax (well, almost all of it--the
password itself won't be recorded).

I got something like:

Option Explicit
Sub testme01()
Dim wks As Worksheet

Set wks = Worksheets("sheet1")
With wks
.Unprotect Password:="hi"
'do your stuff
.Protect Password:="hi", _
DrawingObjects:=True, Contents:=True, Scenarios:=True
End With

End Sub

When you're in the VBE, click on Tools|VBAProject Properties|Protection Tab
give your code (this isn't the worksheet/workbook) it a nice password. Then the
users won't be able to see your code and use the password.

(You might want to try that userinterfaceonly:=true (mentioned in the earlier
message). You protect the worksheet in code. Your macros will be able to make
more changes than the user.

=========
All that said, the worksheet protection within excel isn't made for security
measures--I think it's made to stop data entry errors on locked cells on
protected worksheets. (There's code posted here almost everyday that will
unprotect a protected worksheet.)