Creating an error message?

[azmina]

I have an excel sheet in which most cells are protected. Certain cells are left unprotected for input by various users.

I would like to create an error message when someone tries to override one of the protected cells. (Similar to the error message you can create under Data Validation).

Please help.
Thanks!

 

[erik.van.geit]

Hello, azmina !
Welcome to the Board !!!!!

when the cells or locked and the sheet is protected, you will get a message
isn't this enough ?

kind regards,
Erik

 

[azmina]

creating error messages

I wish it were enough, but I would like to create a personalized one. Any ideas?

A.

 

[erik.van.geit]

could you explain somehow what you are trying to achieve ?

when the user want to change a selected cell, he will anyway get the message
this would just add another message when selecting a locked cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked Then MsgBox "this cell is locked", 48, "title"
End Sub

later excelversions can disable selecting locked cells

please clarify if you need more

 

[XLGibbs]

Actually I just tried something similar and only the standard excel warning comes up. If the property of the cell is locked and the sheet is unprotected the message box does not fire when looking for the Locked property.

Is there a way to disable the standard warning when protection is on? that may be needed prior...

Otherwise, the sheet would have to protected on open and an array of all cells not protected would have to be passed to the selection change event as an array...and then the sheet would have to be unprotected and the selection change event would have to evaluate if the target was present in the array or not...

I dunno though..i thought this would be fairly simple to disable, but maybe I am quite tired...

 

[erik.van.geit]

If the property of the cell is locked and the sheet is unprotected the message box does not fire when looking for the Locked property.

"does not fire" for you
but it is firing for me

 

[XLGibbs]

Yeah, I apparently I had executed a macro a while ago and Events were disabled ...the one to turn them back on I did not run....so that problem was solved...once I enabled events...wheee! msgbox...

I still would like to know if the standard warning when the sheet is protected could be disabled...I am sure it is possible, I just cant seem to nail it down.. Probably something really obvious that I overlooked..

 

[azmina]

Unfortunately, I am not a pro at excel. I have just recently become familiar with various functions within excel.

Just to clarify; I have certain cells locked and when I protect the sheet and try to override the cells, an error message comes up. Essentially, I am looking to do the same thing, except, I would like to personalize the error message for the specific cells.

Any ideas for an amateur?

A.

 

[iggydarsa]

You said you are an amateur but I dont know your level. so if you know VBE, erik.van.geit's 2nd post is the thing you want to do.

 

[erik.van.geit]

Unfortunately, I am not a pro at excel. I have just recently become familiar with various functions within excel.

Just to clarify; I have certain cells locked and when I protect the sheet and try to override the cells, an error message comes up. Essentially, I am looking to do the same thing, except, I would like to personalize the error message for the specific cells.

Any ideas for an amateur?

A.

my code will let popup a message (without getting the standard message) when trying to double-click a locked cell
using function key F2 will still be possible

regular excel-users are well aware of the fact that some (most of them) cells are locked and others are open to edits
I don't see the reason to circumvent the standard message since it tells the user exactly what it should

it seems to me the best thing you could do in your case would be to disable the selection of locked cells
are you using a version which allows to disable the selection of locked cells? In other words: when you protect a sheet, do you get a popup where you can choose from several options ?

See also: Restricting access only to specific cells on a spreadsheet

kind regards,
Erik