Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! =) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REPSOT?? Sub Worksheet_Change(ByVal Target As Range) | Excel Discussion (Misc queries) | |||
Worksheet_change event handler error | Excel Discussion (Misc queries) | |||
Multiple values in Private Sub Worksheet_Change(ByVal Target As R | Excel Discussion (Misc queries) | |||
Control Toolbox and Private Sub Worksheet_Change(ByVal Target As R | Excel Discussion (Misc queries) | |||
Many Sub Worksheet_Change(ByVal Target As Range) In One Worksheet | Excel Programming |