Scroll Area Not Saving When Workbook Closed

    Hi,


    Ive tried disabling scroll on sheet 1 of my workbook by entering the cell range into the properties window in VBA. However when I save the sheet and reopen it, it seems to reset and are able to scroll again.


    I tried using the following:


    [vba]Private Sub Worksheet_Activate()
    Worksheets(1).ScrollArea = "A1:Q51"


    End Sub[/vba]


    and a few variations of the above but this dosnt seem to be working. The only other code I have in the workbook is under "thisworkboko" which is this:


    [vba]Private Sub Workbook_Open()
    Application.DisplayFullScreen = True


    End Sub[/vba]


    so I dont know why its not working


    Any suggestions?


    Thanks in advance

    Re: Disable Scroll Not Saving When Sheet Is Closed


    Hi,


    Read the rules you agreed to and use tags, you may get an answer then.


    John

    Re: Disable Scroll Not Saving When Sheet Is Closed


    Hi,


    Ive tried disabling scroll on sheet 1 of my workbook by entering the cell range into the properties window in VBA. However when I save the sheet and reopen it, it seems to reset and are able to scroll again.


    I tried using the following:


    Code
    Private Sub Worksheet_Activate()
Worksheets(1).ScrollArea = "A1:Q51"


End Sub


    and a few variations of the above but this dosnt seem to be working. The only other code I have in the workbook is under "thisworkboko" which is this:


    Code
    Private Sub Workbook_Open()
Application.DisplayFullScreen = True


End Sub


    so I dont know why its not working


    Any suggestions?


    Thanks in advance

    Re: Disable Scroll Not Saving When Sheet Is Closed


    Here is an example of what I use...
    Think its much the same as yours, just uses a different event to fire it

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'   Limit scrolling area of worksheet
    On Error GoTo ErrorHandler:
'    Debug.Print "shtTimesheet selection change event fired"
        Me.ScrollArea = "$A$12:$G$65536"
ErrorHandler:
    If Err.Number <> 0 Then
        Me.ScrollArea = ""
    End If
End Sub


    Maybe you need to use $ signs to make the reference absolute.


    Make sure

    Code
    Application.EnableEvents = True

    and that the code resides in the codemodule of the workSHEET it is to act on.

    Re: Restict Scroll Area Not Saving When Workbook Is Closed


    The scrollarea does not persist when workbook is closed.


    It's possible you code is not working because the Activate event of the sheet is not being fired. If sheet1 is the activesheet when the workbook opend the Activate event will not happen.
    Instead add the code to the Thisworkbook module in the Open event
    [vba]Private Sub Workbook_Open()


    Worksheets(1).ScrollArea = "A1:Q51"


    End Sub
    [/vba]

    [h4]Cheers
    Andy    [/h4]

    Re: Restict Scroll Area Not Saving When Workbook Is Closed


    thanks for the replies. Its now working thanks Bruce and Andy

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login