customize excel error message

[excel_nubie]

I got the below code from Mr. Excel to protect a cell with data from being overwritten. I also had to highlight all cells & select format, cell, protection & uncheck locked. It works beautifully. I'm so psyched!! The next step is to customize the error message. Can someone help me with this? Many thanks!!

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect "password"
Target.Locked = True
ActiveSheet.Protect "password"
End Sub

 

[Stormseed]

What error message are you referring to ?

 

[excel_nubie]

oh, sorry about not being more clear. When you click on a cell containing data, excel displays a standard msg: "The cell or chart you are trying to change is protected and therefore read only. To modify a protected cell or chart, first remove protection using the Unprotect sheet command...". I'd like to change the text of this message, or stop it from displaying and display my own message. thanks.

 

[Stormseed]

You cannot customize this error message either ways. However, you can stop it from being displayed provided you let us know what are you trying to do, how is your data layout on the worksheet, what cell / cells do you want the user to modify on a protected worksheet ?

 

[excel_nubie]

If there is data in a cell, a person should NOT be able to modify the cell contents.

Can i suppress excel's error, and display my own message?

 

[excel_nubie]

just to clarify, user's can enter data into any BLANK cell. thanks again!

 

[Joe Was]

This only displays a message, it does not stop the edit!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Sheet module code only, like: Sheet1!

If Target.Value <> "" Then
MsgBox "You can only add information to BLANK cells!" & vbLf & _
"Any cell with a value cannot be edited!", _
vbCritical + vbOKOnly, _
"Edit Error!"
Else
End If
End Sub

 

[excel_nubie]

Thanks for the reply. i copied & pasted the code as shown below. I also tried moving it to the top of the code, and again after the first line. Only excel's standard msg displays. Can you help out? thanks very much.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect "password"
Target.Locked = True
ActiveSheet.Protect "password"
If Target.Value <> "" Then
MsgBox "You can only add information to BLANK cells!" & vbLf & _
"Any cell with a value cannot be edited!", _
vbCritical + vbOKOnly, _
"Edit Error!"
Else
End If
End Sub
<!-- / message -->