Prevent deletion of Excel tables.

[tiredofit]

Assume I have the following formatted as an Excel table:

I would to prevent users from deleting the table (essentially prevent row 1 from being deleted).

This is my code but it doesn't work:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Row = 1 Then
    
        Application.EnableEvents = False
        
        Application.Undo
        
        Application.EnableEvents = True
        
    End If
    
End Sub

If the user selects row 1 and presses the delete key (ie clear contents), I see this:

How can I amend my code to prevent users deleting row 1?

Thanks

 

[ranman256]

Lock the sheet?

 

[tiredofit]

Lock the sheet?

don't want to do that.

 

[Akuini]

Try this:
Let's say the header is in A1:C1

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:C1")) Is Nothing Then
        Application.EnableEvents = False
        On Error GoTo Skip:
        Application.Undo
        Application.OnUndo "", ""
        
    End If

Skip:
Application.EnableEvents = True
End Sub

 

[tiredofit]

Try this:
Let's say the header is in A1:C1

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:C1")) Is Nothing Then
        Application.EnableEvents = False
        On Error GoTo Skip:
        Application.Undo
        Application.OnUndo "", ""
       
    End If

Skip:
Application.EnableEvents = True
End Sub

Thanks for the solution, it worked as expected.

However I do have one query relating to your code.

Put a break point on this line:

If Not Intersect(Target, Range("A1:C1")) Is Nothing Then

then try to delete / clearcontents anything in row 1.

The code will hit the break point, as expected.

Step into the code all the way to End Sub.

Then if you press F8 (to step into it AFTER End Sub), the code jumps to this line:

If Not Intersect(Target, Range("A1:C1")) Is Nothing Then

now if you step into it all the way to End Sub, it ends, as expected.

My question is: why does it continue after End Sub the first time?

 

[Akuini]

Actually I don't quite understand this behavior. It looks like if you delete a header it will trigger the Worksheet_Change more than once even if you have set Application.EnableEvents = False. So in your code it will run Application.Undo multiple times which generates wrong result.

What my code does is clear the undo stack with Application.OnUndo "", "" after Application.Undo is executed the first time, so it is only executed once, which gives the correct result.

Maybe some experts here can give a better explanation.

 

[tiredofit]

Actually I don't quite understand this behavior. It looks like if you delete a header it will trigger the Worksheet_Change more than once even if you have set Application.EnableEvents = False. So in your code it will run Application.Undo multiple times which generates wrong result.

What my code does is clear the undo stack with Application.OnUndo "", "" after Application.Undo is executed the first time, so it is only executed once, which gives the correct result.

Maybe some experts here can give a better explanation.

Thanks for your reply.

I've had similar (unusual) experiences with Events in the past.

As you said, hopefully someone else might be able to explain this odd behaviour.