Get page orientation of active sheet in Excel 2007

  • Thread starter Thread starter Luca Brasi
  • Start date Start date
L

Luca Brasi

If a chart is selected in Excel 2007, using
ActiveSheet.PageSetup.Orientation doesn't seem to return the orientation
of the active sheet, but the orientation of the selected chart object
(whatever the meaning of this value is...?).

Is there another way to return the orientation of the active sheet (if
not programmatically unselect the chart shape first)?
Thanks
 
In using this code (bit over the top really) it works fine in Excel 2003
whether the chart is selected or not :-

***************************************************
Sub show_orientation_of_current_sheet()
Dim my_orientation As String

Select Case ActiveSheet.PageSetup.Orientation
Case 1: my_orientation = "Portrait"
Case 2: my_orientation = "Landscape"
Case Else: my_orientation = "Unknown"
End Select

MsgBox "The current sheet is in " & my_orientation

End Sub
***************************************************

If this didn't help then using this line will deselect the chart before you
run your command :-

***************************************************
ActiveSheet.Range("A1").Select
***************************************************

In other words it selects a cell and forces the deselection of the chart.

Hope this helps,
Jack.
 
Jack, thanks for replying. I used to use code like yours in earlier
versions of Excel as well. But in Excel 2007, if a chart is selected,
the orientation of this chart is returned and not of the active sheet
(also if you use the page layout tab on the ribbon).

And I don't want to unselect the chart programmatically because I'm not
able (in 2007) to reselect it after my code has run (a user should not
notice that any code was run).
 
What do you get with activesheet.name?

Dim sh as Worksheet
set sh = worksheets(Activesheet.name)

msgbox sh.pagesetup.orientation

of course, you don't need to select the chart to work with it.

activesheet.chartobjects(1).Select
 
Sub ABCD()
Dim obj As Object
Set obj = Selection
Do While TypeName(obj) <> "Worksheet"
Set obj = obj.Parent
Loop
MsgBox obj.PageSetup.Orientation
End Sub
 
Unfortunately no.

There's no problem to get the reference to the active worksheet. The
ActiveSheet property does this correctly.

But if a chart is selected on this sheet,
ActiveSheet.PageSetup.Oriention does not return the orientation of this
sheet, but the orientation of the selected chart object (whatever that
means). The same for PaperSize.

Thanks for your help anyway.
 
Back
Top