Know if specific columns were deleted

[jacko2401]

Hi

I have a worksheet_change event that I want to put in place but I am having problems ensuring that this event exits if columns A to E are accidentally deleted.

I had the same problem with my code if a row was inserted but I started the event with the following:

Public Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = ActiveSheet.Columns.Count Then Exit Sub

This works fine. However, I am not sure how I can trap the possibility of someone accidentally deleting columns A to E as this will trigger hundreds of message boxes confirming an error that I set up for every cell change. Obviously I do not want this to happen so would like an easy way to either prompt the user to confirm if they want to delete the entire column or allow it and exit the worksheet_change event.

Any help much appreciated.
Thanks

 

[JLGWhiz]

You could use something like:

Code:
If Target.Cells.Count > 1 Then Exit Sub
Code:

That way if the user selects more than one cell to do anything, it will not execute the event code.

 

[iliace]

How are the errors triggered? If it's event-based, you could set a condition when you detect the columns being deleted. In those cases, Target.Address will be something like "$A:$E" (without row numbers). Then, you can do Application.EnableEvents = False, so that events stop processing until the error is resolved.

Honestly, my best advice would be to lock those first five columns and protect the worksheet so that those columns can't be deleted. That should be feasible, so long as you don't have some other specific protection setting.

 

[jacko2401]

The target area can be more than one cell unfortunately as entries get copied/pasted in bulk sometimes. I think iliace's suggestion of protecting those cells so they can be modified but not deed seems to be the way forward. Thanks guys.