How to tell if range is on activesheet or on a fixed/certain sheet?

0
votes

if I 

Dim rng As Range
Set rng = Range("A1")

i know rng is actually ActiveSheet.Range("A1")

then if I

Dim rngsht1 As Range
Set rngsht1 = Worksheets("Sheet1").Range("A1")

then i know rngsht1 is always on Sheet1

So now say I have a range object called "somerange", if I want to know what sheet this range is on I'll probably do this

somerange.worksheet.name

but if it gives me "Sheet1", i can't tell if it's because i have sheet1 active or because somerange is always on sheet1, without having to switch between different sheets and try again.

My question is, is there a simple way to tell whether a range object is on activesheet, or on a fixed/certain sheet? Thanks.

UPDATE: So this question is invalid. Thanks to GSerg I realized that a range object, once created, is ALWAYS on a fixed worksheet which is the worksheet the range object was created on.

2
vbaexcel
Once created, a Range never changes sheets, regardless of what sheet is active. somerange.worksheet is the parent Worksheet object. You can test this reference as is, without looking at its name. - GSerg
OH I GET IT!! i misunderstood the msdn article. So my question is actually invalid since a range object is ALWAYS on a fixed worksheet. Thank you! - lineil

2 Answers

1
votes

Please try to use Is operator (object comparison):

If rangeObject.Worksheet Is ActiveSheet Then
  ' (... your code ...)
End If
0
votes

Question the range's Parent

Sub MAIN()
    Dim rng As Range
    Set rng = Sheets(1).Range("A1")
    Call IsItOnTheActiveSheet(rng)
End Sub

Sub IsItOnTheActiveSheet(r As Range)
    If r.Parent.Name = ActiveSheet.Name Then
        MsgBox "its on the activesheet"
    Else
        MsgBox "its not on the active sheet"
    End If
End Sub