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

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