Display a customized message when trying to edit a locked cell.

[KP_SoCal]

I'm using the code below to protect a range of cells. It allows end users to select a protected cell within range of A1:A10. If the end user attempts to edit the cell, Microsoft's standard message box will fire,

"The cell or chart you are trying to change is protected and therefore read-only..."

What I would like to do is replace Microsoft's default message with my own custom message. Any ideas?

Public Sub ProtectAllSheets()
Dim N As Single
Sheets("Sheet1").Select
Application.ScreenUpdating = False
For N = 1 To Sheets.Count
With Sheets(N)
.Cells.Locked = False
.Range("A1:A10").Locked = True
.Protect 
End With
Next N
Application.ScreenUpdating = True
End Sub

 

[KP_SoCal]

Based on the responses, I'm guess no one knows how to overide Microsoft's default message prompt for protected cells. I've searched multiple forums and it seems the general consensus is that it is not possible.

Not the end of the world though. There is a work around in case anyone is curious. Instead of protecting the cells in my original code, I will just lock them. Then I can apply logic from this thread to achieve the expected end results.

Little extra work, but it gets the job done.

 

[lenze]

You need to protect your cells of interest with a different approach!!!

Private Sub WorkBook_SheetChange(ByVal sh as Object, ByVal Target as Range)
If Intersect (Target, sh.Range("$A$1:$A$10")) Is Nothing Then Exit Sub
MsgBox "You fricking idiot. I've told you you can't change cell " & Target.Address
With Application
    .EnableEvents = False
    .UnDo
    .EnableEvents = True
End With
End Sub

Notice your sheet does not have to be protected

You could also prompt for a Password to allow editing if needed!

lenze

 

[KP_SoCal]

Nice! I like it, and it's less work!

 

[Jalisco72]

Hello Mr Excel,
can you please provide me with the code that works hand in hand with the code that you previously provided?
I am trying to create a macro that prompts the user for a password every time the user tries to edit one of the cells.