Setting ScrollArea in Auto_Open()

[Phil Hageman]

In the following Auto_Open Sub, I need to enter code that
sets ScrollArea for the following worksheets:
"Scorecard" B1:BA45
"Financial" B1:BA96
"L&G" B1:BA96
"Process" B1:BA96
"Metrics: A1:A33

Sub Auto_Open()
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayFullScreen = True

For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then
ws.Select
Application.Goto ws.Range("A1"), True
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayHorizontalScrollBar = False
End If
Next

Worksheets("Customer").Select
ActiveWindow.Zoom = 66
Worksheets("Financial").Select
ActiveWindow.Zoom = 66
Worksheets("Learning and Growth").Select
ActiveWindow.Zoom = 66
Worksheets("Internal Business Process").Select
ActiveWindow.Zoom = 66
Worksheets("Scorecard").Select
ActiveWindow.Zoom = 68

ThisWorkbook.Colors(7) = RGB(255, 124, 128)
Application.AutoPercentEntry = True
Application.ScreenUpdating = True
End Sub

 

[Don Guillett]

Directly from vba HELP
ScrollArea Property
See Also Applies To Example Specifics
Returns or sets the range where scrolling is allowed, as an A1-style range
reference. Cells outside the scroll area cannot be selected. Read/write
String.

Remarks
Set this property to the empty string ("") to enable cell selection for the
entire sheet.

Example
This example sets the scroll area for worksheet one.

Worksheets(1).ScrollArea = "a1:f10"so with NO selections required.

sub auto_open()
worksheets("Scorecard").scrollarea="$B$1:$BA$45"
worksheets(""Financial").scrollarea="$B$1:$BA$96"
etc

"L&G" B1:BA96
"Process" B1:BA96
"Metrics: A1:A33

end sub
--
Don Guillett
SalesAid Software
(e-mail address removed)

In the following Auto_Open Sub, I need to enter code that
sets ScrollArea for the following worksheets:
"Scorecard" B1:BA45
"L&G" B1:BA96
"Process" B1:BA96
"Metrics: A1:A33

end sub
\>

 

[papou]

Hi Phil
Add this to your code:
Select Case ws.Name
Case Is = "Financial", "L&G", "Process"
ws.ScrollArea = "B1:BA96"
Case Is = "Scorecard"
ws.ScrollArea = "B1:BA45"
Case Is = "Metrics"
ws.ScrollArea = "A1:A33"
End Select
PS: btw displaying tabs and scrollbars is associated with your workbook
Hth

Regards
Pascal