0

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.

Community
  • 1
  • 1
lineil
  • 989
  • 1
  • 8
  • 10
  • 3
    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](http://stackoverflow.com/a/11254505/11683), without looking at its name. – GSerg Jan 26 '15 at 22:14
  • 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 Jan 27 '15 at 23:28

2 Answers2

1

Please try to use Is operator (object comparison):

If rangeObject.Worksheet Is ActiveSheet Then
  ' (... your code ...)
End If
  • Thank you i just learnt from GSerg that my question is invalid cuz a range object's ws is always the ws it was created on and does not change. AND I just learnt from you that i can compare objects using `is` operator! Thanks – lineil Jan 27 '15 at 23:34
  • No problem. I'm glad it slightly helped. Regards. –  Jan 28 '15 at 05:50
0

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
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • This will give a false positive if there is another workbook opened at the same time with a sheet of the same name as the range's parent sheet name, and that other sheet happens to be active when you call the code. [`Is`](http://stackoverflow.com/a/28159754/11683) is correct. – GSerg Jan 27 '15 at 08:43