VisibleRange references

[RLang]

Hi,
Is there a way to reference ActiveWindow.VisibleRange functions while an
embedded chart is selected?

Thanks much

 

[Bernie Deitrick]

In general, for a chart, you need the Parent of the parent of the parent

Dim myS As Worksheet
Set myS = Selection.Parent.Parent.Parent
msgbox myS.name & " is the active sheet."


More or fewer .Parent my be needed, depending on what the actual selection is.

Or you could try them all until you don't get an error:

Dim myS As Worksheet

On Error GoTo NotSheet1
Set myS = Selection.Parent.Parent.Parent.Parent
GoTo AllOK

SheetTry2:
On Error GoTo NotSheet2
Set myS = Selection.Parent.Parent.Parent
GoTo AllOK

SheetTry3:
On Error GoTo NotSheet3
Set myS = Selection.Parent.Parent
GoTo AllOK

SheetTry4:
On Error GoTo NotSheet4
Set myS = Selection.Parent

AllOK: MsgBox myS.Name & " is the active sheet."
Exit Sub
NotSheet1:
Resume SheetTry2
NotSheet2:
Resume SheetTry3
NotSheet3:
Resume SheetTry4
NotSheet4:
MsgBox "Not sure what that selection is..."



HTH,
Bernie
MS Excel MVP

 

[RLang]

Hi Bernie,
Thanks for the response. I hadn't used the Parent object before and I
get the logic to navigate to the original worksheet name but I'm still unsure
what the followup command is to call the "VisibleRange" functions to
determine what portion of the spreadsheet and embedded chart is visible to
the user without deselecting the chart and selecting the spreadsheet itself
to call the command. I want to be able to do this without de-selecting the
chart series. The only way so far that I can see to use the VisibleRange
functions is through the ActiveWindow object and this errors out whenever any
part of the chart is selected.