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!

 

[Jaafar Tribak]

Azmina,

You mentioned DATA VALIDATION. You can actually set up a Custom Vaidation for the Cells you want locked using the following formula which will always return False :

=True=False

You can then personalize your error message as you wish via the Data VAlidation Error Alert tab.

No sheet protection required :wink:

Regards.

 

[azmina]

Is it possible to allow the pop up to come up for only certain locked cells, and leave the other locked cells as is (with the condition that the user can only select unlocked cells under the protection menu)?

Thanks for all your help.
A.

 

[azmina]

Wow! Thanks! It worked!

Another question...I have a cell that needs to be calculated based on user input in two other cells. I would like an error message to pop up if those cells are left empty.

Any ideas?

Thanks again!!!
A.

 

[azmina]

Another Question...Data Validation

So, I can get an error message to pop up if someone tries to override a cell by using Data Validation and the formula =True=False. This formula works if someone tries to replace the cells with other data, however, if the contents of the cell are deleted entirely, the error message does not come up. Any idea?

I still also need help with doing a conditional format that activates if two other dependent cells are left empty.

Thanks for all your help

A.

 

[erik.van.geit]

validation is not waterproof as you just saw
also you could paste (values) without any problem

you didn't give feedback to my previous post, but perhaps you're not intrested in these solutions ?

 

[azmina]

Hi,
Sorry for not responding to your earlier suggestion. I have no experience with VBE, I wouldnt know where to begin. I guess I was looking for a simple solution.

 

[erik.van.geit]

step by step about VBA
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the following code:

Sub a()
Range("A1").Select
ActiveCell = "this is the first cell of the sheet"
MsgBox Range("A1"), 64, "Contents of cell " & ActiveCell.Address
End Sub

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)

How to learn with example-codes...
1. use the macro recorder to see how code is generated (such code will need some "cleaning" afterwards)
2. "step" through it with the function key F8, while observing what's going on on your sheet
3. click on an item and hit F1 to let popup the help

The code you received in my second reply has to be installed in the sheet code window
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE