macro for cell locked

[massimo]

Hello to all from Italy.
I am a new member and I hope my English (translated with google translator) is understandable.
I ask help for editing a macro found the net:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Locked Then
MsgBox "You cannot edit this cell", vbCritical

    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True

End If
End Sub

it's possible add to
Application.Undo
for example, the block of the color of the cells - the addition of new rows or columns etc ....
I hope that was understandable.
max
__________________________________________________________________

Mod edit : post moved to appropriate forum …​

 

[Marc L]

Hi !

If worksheet is protected, can you change a locked cell ?
No ! So no need any code …
​

 

[massimo]

Ciao,
worksheet is not protected.

 

[Marc L]

I suppose you read VBA help of Undo method …


​

 

[massimo]

Hello,
I changed the macro, I have now entered:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Locked And ActiveSheet.Protect Then

ActiveSheet.Unprotect "123456"
Application.EnableEvents = False
MsgBox "questa cella è bloccata!!!", vbCritical + vbOKOnly, " ERRORE!"
Application.EnableEvents = True
ActiveSheet.Protect "123456"
End If
 
End Sub

'--------------------------------------------------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
'ActiveSheet.Unprotect "123456"
If Target.Locked Then
 
MsgBox "questa cella è bloccata!!!", vbCritical + vbOKOnly, " ERRORE!"
 
End If

'ActiveSheet.Protect "123456"
End Sub

because clicking CANC show the message to excel "The cell or chart you are editing...."?
you can not see this message?
max

 

[Marc L]

Maybe with DisplayAlerts set to False …


​

 

[massimo]

Hello, I tried

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

      'ActiveSheet.Unprotect "123456"
 
Application.DisplayAlerts = False
If Target.Locked Then

Application.EnableEvents = False
MsgBox "questa cella è bloccata!!!", vbCritical + vbOKOnly, " ERRORE!"
Application.EnableEvents = True
 
End If

      'ActiveSheet.Protect "123456"
                     
End Sub

appears always notice excel.
max