Creating an error message?

azmina

New Member
Joined
Dec 7, 2005
Messages
7
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!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

New Member
Joined
Dec 7, 2005
Messages
7
creating error messages

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

A.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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

Well-known Member
Joined
Feb 25, 2005
Messages
2,446

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Code:
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

Well-known Member
Joined
Feb 25, 2005
Messages
2,446

ADVERTISEMENT

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

New Member
Joined
Dec 7, 2005
Messages
7
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

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
azmina said:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,822
Messages
5,574,520
Members
412,600
Latest member
Andyb2
Top