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!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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
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
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,649
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,171,794
Messages
5,877,555
Members
433,265
Latest member
Umaratnam

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
Top