Protect certain cells

[Thorsten Walenzyk]

I would like to know, if there is any other way to protect some cell than
using the Cell.Locked property and setting the .Proptect peroperty for the
sheet.

Protecting the whole sheet is not really sufficient for me, because you are
not allowed to use some nice features on a protecte sheet (like the nice
AutoFilter-function).

Thanks Thorsten

 

[Sharad]

This can not be done, by setting some properties in excel, and you will
need to look for a work around.

One of the possible work arround could be writing a code in that
Worksheet_SelectionChange event procedure.
Just for example, if the cells you want to protect are in say column E
then

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableCancelKey = xlDisabled
If Target.Column = 5 Then
Target.Offset(0, 1).Select
Msgbox "Please leave that cell alone."
End If
Application.EnableCancelKey = xlInterrupt
End Sub

You can accrodingly modified the code for cells you want to protect.

Sharad

 

[Norman Jones]

Hi Thorsten,

Protecting the whole sheet is not really sufficient for me, because you
are
not allowed to use some nice features on a protecte sheet (like the nice
AutoFilter-function).

You can apply protection and enable the AutoFilter with:

With ActiveSheet
.EnableAutoFilter = True
.Protect userinterfaceonly:=True, _
password:="YourPassword"
End With

As the UserInterfaceOnly setting is not persistent between sessions, the
code should be included in the Workbook_Open or Auto_Open procedures.

 

[Thorsten Walenzyk]

This seems to work for me.

It would be nice to just unselect the selection in case of a 'locked' cell.
Is that is possible???

 

[Tom Ogilvy]

If the user disables macros, it will leave your cells unprotected anyway.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldRange as Range
Dim bLocked as Boolean
on Error goto ErrHandler
if OldRange is nothing then
Set OldRange = Range("A1")
End if
Application.EnableCancelKey = xlDisabled
bLocked = False
for each cell in Target
if cell.Locked then
bLocked = True
exit for
end if
Next
If bLocked Then
Application.EnableEvents = False
OldRange.Select
Else
set OldRange = Target
End If
ErrHandler:
Application.EnableCancelKey = xlInterrupt
Application.enableEvents = True
End Sub