VisibleRange references
Hi,
Is there a way to reference ActiveWindow.VisibleRange functions while an embedded chart is selected? Thanks much |
VisibleRange references
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" wrote in message ... Hi, Is there a way to reference ActiveWindow.VisibleRange functions while an embedded chart is selected? Thanks much |
VisibleRange references
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. "Bernie Deitrick" wrote: 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" wrote in message ... Hi, Is there a way to reference ActiveWindow.VisibleRange functions while an embedded chart is selected? Thanks much |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com