XL Sheet Display

[someone]

Hi,
I am using Excel 2003.
For some sheets I do not want the Row & Column Headers showing, so in
Tools > Options > View then in the "Window options" I uncheck the box "Row
& Column headers". By doing this I can set each sheet individually. Some
have the headers showing others not.
What I want to do is a similar thing for the "Horizontal scroll bar" and
also the "Vertical scroll bar" settings. However when I uncheck these boxes,
the scroll bar for ALL sheets is not shown. I cannot set each sheet
individually, ie some showing and some not showing the scroll bars.
Can what I am attempting to do actually be done? Is it a feature of
Excel 2003 that this is the way it is? Is there anything else that I can do?
Any help or suggestions will be very much appreciated.
Thanks.
Brian.

 

[Don Guillett]

You could have a sheet_activate macro to hide and then unhide on deactivate

 

[Dave Peterson]

You could use a couple of event macros. You'd want them to check when you
changed sheets--or when you changed workbooks.

If you want to try, this would go in the ThisWorkbook module:

Option Explicit
Dim SheetNames As Variant
Private Function OnSheetNamesList(ShtName As String) As Boolean

Dim res As Variant

If IsArray(SheetNames) = False Then
Call SetArray
End If

res = Application.Match(ActiveSheet.Name, SheetNames, 0)

OnSheetNamesList = CBool(IsNumeric(res))

End Function
Private Sub Workbook_Activate()
Call DoTheWork _
(ShouldBeVisible:=OnSheetNamesList(ShtName:=ActiveSheet.Name))
End Sub
Private Sub Workbook_Deactivate()
Call DoTheWork(ShouldBeVisible:=True)
End Sub
Private Sub Workbook_Open()
If IsArray(SheetNames) = False Then
Call SetArray
End If
End Sub
Private Sub SetArray()
'list of names that show the scrollbars
SheetNames = Array("sheet1", "sheet3", "sheet5")
End Sub
Private Sub DoTheWork(ShouldBeVisible As Boolean)
With ActiveWindow
.DisplayHorizontalScrollBar = ShouldBeVisible
.DisplayVerticalScrollBar = ShouldBeVisible
End With
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call DoTheWork(ShouldBeVisible:=OnSheetNamesList(ShtName:=Sh.Name))
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[someone]

Hi Don & Dave,
Thank you for your suggestion.
I have done some work with macros but this one is advanced for me. It
might take me a while to get it right but I will let you know what success I
have.
Your help is very much appreciated. Thank you again.
Brian.