Link to home
Start Free TrialLog in
Avatar of morinia
moriniaFlag for United States of America

asked on

setting scroll area in Excel workbook using VB


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?
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

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()
ActiveWindow.ScrollColumn = 1
End Sub

Open in new window

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
        Selection.EntireColumn.Hidden = xlVeryHidden
    ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=True
End Sub

Open in new window

Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Martins suggestion will work as well, but you will get a grey area to the right of Column M.
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.
User generated image
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.
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.