Protecting a Range in a list

[John]

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

 

[Mike Fogleman]

I believe the code Nick mentioned was
If Target.Columns.Count = Columns.Count

Are you letting the user delete rows in the "Protected" area?
Mike F

 

[Dave Peterson]

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

 

[Mike Fogleman]

This will protect the list from individual cell changes, but will allow a
list member to be deleted only if the entire row is selected. It will also
allow multiple rows to be deleted at one time, so it is possible to delete
the entire list if all their rows are selected.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range

Set myRng = Me.Range("Protect")
Application.EnableEvents = False
On Error GoTo errHandler:
If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
ElseIf Target.Columns.Count = Columns.Count Then
'let 'em do it
Else
Application.Undo
MsgBox "I've asked you not to change this Field!"
End If
On Error GoTo errHandler:
errHandler:
Application.EnableEvents = True
End Sub

Mike F