Mmmmmm.

First, oops. I got the code for each routine in the wrong one.

The code for the worksheet_Change was under _selectionchange (and vice versa).
(well, it looks that way today! But maybe someone changed it after I posted. I
blame the gremlins.)

It should have read:

Option Explicit
Private Sub Worksheet_selectionChange(ByVal Target As Range)

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

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
Application.EnableEvents = False
Me.Range("a1").Select
Application.EnableEvents = True
MsgBox "Please don't get near that radio active range!"
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

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

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 range!"
End If

errHandler:
Application.EnableEvents = True

End Sub

But I think I would only use one of them (the current! worksheet_change if I had
my choice.)

Sorry about my confusion.

===
When I was testing, the .undo undid my range naming (the last thing I did). It
was sure irritating.



wrote:

Boy, this is great! Thanks. I like your idea -- the second approach
-- better. Not only will it be good to let the users select the
protected range so they can copy it, but the .undo touch makes me feel
much more comfortable about not being able to protect this workbook.

The only problem with how it functions now is that after .undo
reverses the edit, if the user selects another cell in the protected
range, Excel re-does the edit (maybe undoing the undo?), and it can
get caught in a loop. Alternately, is the .undo action *itself*
considered a selectionchange, so might it be triggering itself to
fire?

If I save the workbook, the loop stops. Until it is saved, it keeps
reiterating the undo/redo loop whenever another cell in the protected
range is selected.

Is there some way to clear the undo buffer right after it executes?

Dave Peterson wrote in message ...
That may work.

You could stop them from selecting or you could let them select, but stop them
from changing. (But both these techniques fail if the user disables macros or
turns off events.)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

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

If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
Application.EnableEvents = False
Me.Range("a1").Select
Application.EnableEvents = True
MsgBox "Please don't get near that radio active range!"
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

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 range!"
End If

errHandler:
Application.EnableEvents = True

End Sub

wrote:

[snip]

--

Dave Peterson