Maybe replace this line
If Target.Columns.Count = Columns Then
with
If Target.Columns.Count = me.Columns.count Then

And maybe setting up a flag would be an easier way to see what's happening.

I think...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim OkToContinue As Boolean

Set myRng = Me.Range("Protect")

On Error GoTo errHandler:

OkToContinue = False
If Intersect(Target, myRng) Is Nothing Then
OkToContinue = True
End If

If Target.Columns.Count = Me.Columns.Count Then
OkToContinue = True
End If

If OkToContinue = True Then
'let 'em do it
Else
With Application
.EnableEvents = False
.Undo
End With
MsgBox "I've asked you not to change this Field!"
End If

errHandler:
Application.EnableEvents = True

End Sub



John wrote:

Whats wrong with this code? I am using it on a sheet that has an Excel
list. I cant protect the worksheet as it makes the list inactive. The
code gives the user a message and applys an undo whenever they attempt
to type over a formula. I'm trying to allow the users to delete whole
rows and using this code "If Target.Columns.Count = Columns Then". If I
take the delete rows bit out everything works fine, with it in it works
okay but slows the spreadsheet down massivley making it unworkable.

Here is the code:

Dim myRng As Range
Set myRng = Me.Range("Protect")

On Error GoTo errHandler:
If Intersect(Target, myRng) Is Nothing Then
'let 'em do it

Else
With Application
.EnableEvents = False
.Undo
End With
MsgBox "I've asked you not to change this Field!"
End If

On Error GoTo errHandler:
If Target.Columns.Count = Columns Then

Else
With Application
.EnableEvents = False
.Undo
End With
MsgBox "I've asked you not to change this Field!"

End If

errHandler:
Application.EnableEvents = True


End Sub

--

Dave Peterson