Error with Target.Name.Name in Worksheet_Change event
 

Hi!

I wish to prevent users from changing the contents of 2 cells, named "st_xs" and "distrib". My current code is as follows:

-----------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Name.Name = "st_xs" or Target.Name.Name = "distrib" Then

Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True

MsgBox "Sorry, You are not allowed to change this cell!", vbCritical, "Permission Denied!"

End If

End Sub

-----------------------------------------------------------

This code works fine when the user changes those 2 cells, but when another cell is changed, I get an Object/Range Error ('1004')...

I know that using Target.Address instead of Target.Name.Name does the trick, but I'd like to avoid this if possible, since I'd like to let users insert cells/rows etc.

Can someone please help?

Thanks very much in advance!

SuperJas.

Error with Target.Name.Name in Worksheet_Change event
 

SuperJas,

If you've named the two ranges, it appears that you have, you
should be able to use the
Range(RNGNAMEHERE).address along with the Target.address to see if user
has made a change in the wrong cell.

I use this method often. The only problem that I have is if the
actual range is merged with other cells. However, it isn't that much of
a problem either.






*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Error with Target.Name.Name in Worksheet_Change event
 

Thanks very much Barry!

With your help, I've found that this code works like a charm!

----------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cross1 As Range
Dim Cross2 As Range

Set Cross1 = Intersect(Target, Range("st_xs"))
Set Cross2 = Intersect(Target, Range("distrib"))

If Not Cross1 Is Nothing Or Not Cross2 Is Nothing Then

Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True

MsgBox "Sorry, You are not allowed to change this cell!", vbCritical, _
"Permission Denied!"

End If

End Sub
----------------------------------------------------------------------

Thanks again! =)