HI

This may be an impossible thing to do....

When I try to access a protected cell , I am given the standard Excel
popup saying that it is protected and that I can modify it by putting a
password in via the tools menu.

I do realise that the text in the popup can't be changed , but I was
wondering if the popup itself could be replaced with a message box or
validation popup whenever access to a protected cell is attempted?

Perhaps a small macro applying to all the protected parts for the sheets
could be triggered to replace the standard popup with a new one with a
customised message. (One that doesn't suggest users go digging around
trying to unprotect cells...)

Grateful for any help.

Maybe you could stop the users from selecting a locked cell on a protected
worksheet.

If all your users are using xl2002+, then there's an option in the
Tools|Protection|protect sheet dialog that allows you to specify if the user can
select unlocked cells.

If you're using xl2k or below, you'd need a macro:

Sub Auto_Open()
With Worksheets("Sheet99999")
.Unprotect Password:="hi"
.EnableSelection = xlUnlockedCells
.Protect Password:="hi", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End With
End Sub

In fact, in xl2k and below, this setting isn't remembered when you close the
workbook and reopened. Setting it in code was the only way to do this.

Colin Hayes wrote:

HI

This may be an impossible thing to do....

When I try to access a protected cell , I am given the standard Excel
popup saying that it is protected and that I can modify it by putting a
password in via the tools menu.

I do realise that the text in the popup can't be changed , but I was
wondering if the popup itself could be replaced with a message box or
validation popup whenever access to a protected cell is attempted?

Perhaps a small macro applying to all the protected parts for the sheets
could be triggered to replace the standard popup with a new one with a
customised message. (One that doesn't suggest users go digging around
trying to unprotect cells...)

Grateful for any help.

--

Dave Peterson

Hi Dave

OK thanks. I did think of that , but it doesn't help me because they
complain then that they can't select the cell...!

I do need a popup each time a protected cell is selected saying ' You
can't change this cell , and don't need to access it' , rather than
saying ' go and dig around in the options panel for a password..'

Not sure it's possible.

Thanks again.





In article , Dave Peterson
writes
Maybe you could stop the users from selecting a locked cell on a protected
worksheet.

If all your users are using xl2002+, then there's an option in the
Tools|Protection|protect sheet dialog that allows you to specify if the user can
select unlocked cells.

If you're using xl2k or below, you'd need a macro:

Sub Auto_Open()
With Worksheets("Sheet99999")
.Unprotect Password:="hi"
.EnableSelection = xlUnlockedCells
.Protect Password:="hi", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End With
End Sub

In fact, in xl2k and below, this setting isn't remembered when you close the
workbook and reopened. Setting it in code was the only way to do this.

Colin Hayes wrote:

HI

This may be an impossible thing to do....

When I try to access a protected cell , I am given the standard Excel
popup saying that it is protected and that I can modify it by putting a
password in via the tools menu.

I do realise that the text in the popup can't be changed , but I was
wondering if the popup itself could be replaced with a message box or
validation popup whenever access to a protected cell is attempted?

Perhaps a small macro applying to all the protected parts for the sheets
could be triggered to replace the standard popup with a new one with a
customised message. (One that doesn't suggest users go digging around
trying to unprotect cells...)

Grateful for any help.

You can try this event in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then MsgBox "Sorry this cell is locked"
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Colin Hayes" wrote in message ...

Hi Dave

OK thanks. I did think of that , but it doesn't help me because they
complain then that they can't select the cell...!

I do need a popup each time a protected cell is selected saying ' You
can't change this cell , and don't need to access it' , rather than
saying ' go and dig around in the options panel for a password..'

Not sure it's possible.

Thanks again.





In article , Dave Peterson
writes
Maybe you could stop the users from selecting a locked cell on a protected
worksheet.

If all your users are using xl2002+, then there's an option in the
Tools|Protection|protect sheet dialog that allows you to specify if the user can
select unlocked cells.

If you're using xl2k or below, you'd need a macro:

Sub Auto_Open()
With Worksheets("Sheet99999")
.Unprotect Password:="hi"
.EnableSelection = xlUnlockedCells
.Protect Password:="hi", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End With
End Sub

In fact, in xl2k and below, this setting isn't remembered when you close the
workbook and reopened. Setting it in code was the only way to do this.

Colin Hayes wrote:

HI

This may be an impossible thing to do....

When I try to access a protected cell , I am given the standard Excel
popup saying that it is protected and that I can modify it by putting a
password in via the tools menu.

I do realise that the text in the popup can't be changed , but I was
wondering if the popup itself could be replaced with a message box or
validation popup whenever access to a protected cell is attempted?

Perhaps a small macro applying to all the protected parts for the sheets
could be triggered to replace the standard popup with a new one with a
customised message. (One that doesn't suggest users go digging around
trying to unprotect cells...)

Grateful for any help.

In article , Ron de Bruin
writes
You can try this event in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then MsgBox "Sorry this cell is locked"
End Sub




Hi Ron

Yes , that would do it. Seems to be single click rather than double as
in the standard Excel popup.

Is there a way to apply it to the whole workbook at one go , or do I
need to put it in every worksheet separately , do you know?

Great stuff Ron - Thanks.

Remove any code that you added to any of the worksheets.

Put this behind the ThisWorkbook module.

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

If Sh.ProtectContents = True _
Or Sh.ProtectDrawingObjects = True _
Or Sh.ProtectScenarios = True Then
If Target.Cells(1).Locked = True Then
MsgBox "Sorry, the activecell cell is locked"
End If
End If

End Sub

===
Just my opinion--as a user, I would soon tire of this.


Colin Hayes wrote:

In article , Ron de Bruin
writes
You can try this event in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then MsgBox "Sorry this cell is locked"
End Sub



Hi Ron

Yes , that would do it. Seems to be single click rather than double as
in the standard Excel popup.

Is there a way to apply it to the whole workbook at one go , or do I
need to put it in every worksheet separately , do you know?

Great stuff Ron - Thanks.

--

Dave Peterson

Colin

In Thisworkbook module, not in a sheet module.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
If Target.Locked = True Then MsgBox "Sorry this cell is locked"
End Sub


Gord Dibben MS Excel MVP

On Sun, 27 May 2007 19:58:20 +0100, Colin Hayes
wrote:

In article , Ron de Bruin
writes
You can try this event in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then MsgBox "Sorry this cell is locked"
End Sub




Hi Ron

Yes , that would do it. Seems to be single click rather than double as
in the standard Excel popup.

Is there a way to apply it to the whole workbook at one go , or do I
need to put it in every worksheet separately , do you know?

Great stuff Ron - Thanks.