Hide Scroll Bars only for specific Sheets?

Question

Is it possible to use a Macros to hide the Scroll Bars only on a few Sheets, instead of the entire Workbook?

Answer 1

You could just set the scroll range for those sheets.  But you can simulate turning them off for certain sheets with some Workbook event handling code.  All of this goes into the ThisWorkbook code module (if you need help with that, just say so - although this page has instructions on how to do it: http://www.contextures.com/xlvba01.html so that might help).

Here is the code.  You have to be careful not to leave them turned off if you are working with multiple windows or workbooks.  The code should handle all of that. 

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

  Select Case Sh.Name

    Case Is = "Sheet1", "Sheet2" ' names as shown on their tabs

      'turn scroll bars off for the indicated sheets

      With ActiveWindow

      .DisplayHorizontalScrollBar = False

      .DisplayVerticalScrollBar = False

      End With

    Case Else

      'turn scroll bars back on for all other sheets

      With ActiveWindow

      .DisplayHorizontalScrollBar = True

      .DisplayVerticalScrollBar = True

      End With

  End Select

End Sub

Private Sub Workbook_Deactivate()

  'this should handle Close and/or selecting another workbook

  With ActiveWindow

  .DisplayHorizontalScrollBar = True

  .DisplayVerticalScrollBar = True

  End With

End Sub

Private Sub Workbook_Open()

  'because you don't get a SheetActivate event if

  'a specific sheet is already selected when you

  'open a workbook, this tests to see if the

  'active sheet is one you want the scroll bars

  'turned off for.

  Select Case ActiveSheet.Name

    Case Is = "Sheet1", "Sheet2" ' names as shown on their tabs

      'turn scroll bars off for the indicated sheets

      With ActiveWindow

      .DisplayHorizontalScrollBar = False

      .DisplayVerticalScrollBar = False

      End With

    Case Else

      'turn scroll bars back on for all other sheets

      With ActiveWindow

      .DisplayHorizontalScrollBar = True

      .DisplayVerticalScrollBar = True

      End With

  End Select

End Sub

Answer 2

Here's some more information on another way to deal with it on a sheet by sheet basis by setting the ScrollArea property.  You'd have to play with it to see how it would work for you to effectively disable the scroll bars.

http://www.brainbell.com/tutorials/ms-office/excel/Limit_The_Scrolling_Range_Of_Your_Worksheet.htm