Protect certain cells
 

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

Protect certain cells
 

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Protect certain cells
 

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.

---
Regards,
Norman



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

Protect certain cells
 

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???



"Sharad" wrote in message
...

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Protect certain cells
 

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

--
Regards,
Tom Ogilvy

"Thorsten Walenzyk" wrote in message
...
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???



"Sharad" wrote in message
...

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!