Help with ByVal and application.undo

[Vethis]

I am a total rookie and trying to solve an issue on a spreadsheet we use. The initial issue was when I had the column locked the sorting wasn't working properly. I tried a few methods that I found and had the best result by forcing anything entered into the column to be undone. The column has a formula in it and this also prevents that from being deleted; but not in all cases.

The issue I have is that if you select column C and other columns at the same time and hit delete the undo action isn't working and the formula gets wiped out.

This is what I have currently, any help would be awesome!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, Trgt As Range, MyRow As Long

Set MyRange = Intersect(Target, Range("c2:c498,a1:j1"))
Set Trgt = Range("c499")
If Target.Locked = True Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub

 

[JoeMo]

try changing this line:

If Target.Locked = True Then

to this:

If Target.Locked = True or Target.Count > 1 Then

 

[Vethis]

JoeMo, you are the best! That works perfectly. Thank you so much for the quick reply and great solution.

Cheers,

 

[JoeMo]

JoeMo, you are the best! That works perfectly. Thank you so much for the quick reply and great solution.

Cheers,

You are welcome - thanks for the reply.

 

[Vethis]

I ran into a new issue where this prevents deleting multiple cells from other columns that don't include column C. Is there away to make this apply to just Column C, it is the only one I need to protect from being deleted on its own or when selected with others. Anything that doesn't include column C I would like to be able to delete multiple cells at a time.

 

[JoeMo]

I ran into a new issue where this prevents deleting multiple cells from other columns that don't include column C. Is there away to make this apply to just Column C, it is the only one I need to protect from being deleted on its own or when selected with others. Anything that doesn't include column C I would like to be able to delete multiple cells at a time.

Change this line:

If Target.Locked = True or Target.Count > 1 Then

to this:

If (Target.Locked = True) or (Not Intersect(Target, columns("C"))) is Nothing Then