How to hide/show row/column headers and sheet tabs.

[Mark Flaxman]

Hi,

I know how to goto Tools/Options/View to change whether
or not a worksheet displays Row&Column headers, Sheet
tabs, and the Horizontal & Vertical scroll bars, but can
I set these attributes differently for different
worksheets?

Seemingly not in Tools/Options as the result appears
random, ie in worksheet 1, (for instance), if I hide the
row/column headers they are not hidden in worksheets 2
and 3 etc, although if I hide sheet tabs in one
worksheet, they are hidden in all.

I use macros to navigate to different worksheets, and I
need to set or unset these attributes differently,
depending which worksheet is being visited.

If there is a way I assume it will be VBA, but what would
the code be?

Many thanks

Mark Flaxman

 

[Rob van Gelder]

Turn on the Macro Recorder and play with the Tools | Options | Window
options

eg.
Sub Macro1()
With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With
End Sub

 

[Gord Dibben]

Mark

I would place code in the worksheets so that when a particular sheet is
activated, the attributes would be set.

Private Sub Worksheet_Activate()
With ActiveWindow
.DisplayHeadings = True 'change to False in other sheet
.DisplayWorkbookTabs = True
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With
End Sub

Remember: if sheet tabs are False on a sheet you can use CRTL + PageDown or
PageUp to switch sheets.

Gord Dibben Excel MVP

 

[Mark Flaxman]

Excellent!

Many thanks Gord and Rob.

Much appreciated.

Mark