Protection Message

[LEKKERDING]

Hi,

I have sheet1 protected. And I would like to know if someone wants to edit a cell between A6:A12 OR C6:C12 I can get my own Protection Message instead of the original from Excel. But if it's one of the other cells it will give the original one. As my own I would the title to be:"Protection Error" and as message:"You can't change this cell, ask your manager for help".

Hope someone can help me.
Thanks

 

[Dragracer]

There are a few different ways to handle this.

The first would be to use Data | Validation. Leave the settings at "any value" since the sheet is protected and I assume the cells locked. Put your warning in the input message tab. The message will appear when the cell is selected. No VBA required. This would be my choice since it will show up as soon as the cell is selected and disappears as soon as a new cell is selected.

You could through VBA allow only unlocked cells to be selected, this way the default warning would never show up.

The third would be to use the worksheet_SelectionChange event. Put this into you worksheet module. The message will appear weather the sheet is protected or not, but it should start you in the right direction if you want to expand it.<Pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rng As Range

Set rng = Range("$A$6:$A$12,$C$6:$C$12")

If Not Intersect(Target, rng) Is Nothing Then MsgBox _
"You can't change this cell, ask your manager for help", , "Protection Error"


End Sub</pre>

Post back if you still need help.
This message was edited by Dragracer on 2002-10-07 20:22

 

[Yogi Anand]

Hi Dragracer:

Good call!

In case of Data Validation, I also want to propose that cells A6:A12, and C6:c12 can be unprotected ... and

SETTINGS -- ALLOW Custom; Formula = len(A6)=0

for ERROR ALERT
STYLE -- Stop
TITLE -- Protection Error
ERROR_MESSAGE -- You can't change this cell -- ask your manager for help!

This should nicely fit OP's bill provided Data Validation is an acceptable option.

And your VBA solution is a beauty -- short and sweet.

Regards!

Yogi

 

[Dragracer]

Thanks Yogi

I had thought of offer the same suggestion you did regarding unprotecting the sheet and adding the formula to the data validation. The only draw back as you know is the user can still cut and paste into those cells and destroy the data validation and overwrite the contents. The OP should be aware of this if he wants to go that route. It would still be my first choice.

It would depend on how trusting the supervisor is and the skill and honesty of the users.