Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing Range from other Sheets
I have the following code in Sheet1 which is where the range Priority exists.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Range("Priority"), Target) Is Nothing Then Call FunctionOne Else Call FunctionTwo End If End Sub If I am in a different Sheet and run the same code testing the named range "Priority" I get this error message: Method 'Range' of object'_worksheet' failed. Is there a way to make it so I can test the Range from any Sheet in the file so this code will work regardless of the sheet I am running it in. Note: the range name I am testing will definitely exist. It just may not exist in the current active sheet. Thank you for your help. Steven |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing Range from other Sheets
Hi,
Your Target cell is never going to be in the named range if it is on another sheet. Would you not therefore just call FunctionTwo? i.e; Private Sub Worksheet_SelectionChange(ByVal Target As Range) Call FunctionTwo End Sub Since you Worksheet code is on each sheet. If you want the same code on each sheet I guess you could try the following; Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Parent.Name<Range("Priority").Parent.Name Then Call FunctionTwo Else If Not Intersect(Range("Priority"), Target) Is Nothing Then Call FunctionOne Else Call FunctionTwo End If End If End Sub Regards, Chris. -- Chris Marlow MCSD.NET, Microsoft Office XP Master "Steven" wrote: I have the following code in Sheet1 which is where the range Priority exists. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Range("Priority"), Target) Is Nothing Then Call FunctionOne Else Call FunctionTwo End If End Sub If I am in a different Sheet and run the same code testing the named range "Priority" I get this error message: Method 'Range' of object'_worksheet' failed. Is there a way to make it so I can test the Range from any Sheet in the file so this code will work regardless of the sheet I am running it in. Note: the range name I am testing will definitely exist. It just may not exist in the current active sheet. Thank you for your help. Steven |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TESTING A RANGE OF CELLS | New Users to Excel | |||
Testing same cell across multiple sheets for a string and counting each instance? | Excel Worksheet Functions | |||
Testing a person's age to be within a range | Excel Worksheet Functions | |||
testing whether the ActiveCell is in a given range | Excel Programming | |||
Testing for existence of range | Excel Programming |