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?
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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

0
Martin LissOlder than dirtCommented:
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

0
Roy CoxGroup Finance ManagerCommented:
ScrollArea needs to be reset each time the workbook is opened. Place tthis code in the WorkBook Open event of the WorkBook module

Option Explicit

Private Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.ScrollArea = "A1:M" & ws.Rows.Count
    Next ws
End Sub

Open in new window


Where to paste the code
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Roy CoxGroup Finance ManagerCommented:
Martins suggestion will work as well, but you will get a grey area to the right of Column M.
0
Martin LissOlder than dirtCommented:
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.
No difference
0
Roy CoxGroup Finance ManagerCommented:
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
0
Martin LissOlder than dirtCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.