asked on

setting scroll area in Excel workbook using VB

Experts,

I want to set the scroll area in an Excel workbook on all tabs to display only "A:M". I manually set it but it appears wheh the spreadsheet is saved and reopened, I lose those settings.

Can someone give me the code to set these in VB so they will always be there when the spreadsheet is opened?

Saurabh Singh Teotia

You can put this code in thisworkbook module of your workbook..Assuming you want to do this for sheet1 this will do what you are looking for..once you open the workbook this will select range("a1") and will adjust the column as well...

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Range("a1").Select
ActiveWindow.ScrollColumn = 1
End Sub

Open in new window

Martin Liss

Put this code in ThisWorkbook. Only columns A to M will be displayed and won't be able to be unhidden by the user. Change "password" to whatever you want it to be.

Private Sub Workbook_Open()
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        ws.Select
        ws.Columns("N:XFD").Select
        Selection.EntireColumn.Hidden = xlVeryHidden
    Next
    ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=True
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

Martins suggestion will work as well, but you will get a grey area to the right of Column M.

Martin Liss

Roy, that's not true about the grey area. Book8 show a workbook after my code is executed and Book9 shows a workbook that has been scrolled to XFD1 and there's no difference.

Roy Cox

I wasn't suggesting that your method is wrong, I've used it myself many times. By hiding the columns a large grey area is created to the right of M., By locking the scroll area the user cannot scroll to the other columns but they are still visible. but not accessible.
Book1.xlsm

Martin Liss

I didn't think you were and I'm also not objecting at all about the selection by the Asker of your solution rather than mine. All I was pointing out in post ID: 40837246 was that your "you will get a grey area to the right of Column M" comment wan't correct as can be seen in Book9 which is the lower of the two workbooks shown in post ID: 40837246. Note that the columns are hidden using xlVeryHidden.

I think if either one of us wants to continue this discussion we should do it via EE Messages.