Protection Message

LEKKERDING

New Member
Joined
Oct 6, 2002
Messages
7
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
 
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top