Limit scroll area permanently

[leopardhawk]

Hello forum friends, I am so frustrated but have nowhere else to turn. Everything I have tried doesn't work as it should. I am trying to limit the scroll area on a dozen or so sheets in my workbook but I'll focus on one for now. I have searched the Internet and this site, watched several videos and tried every solution as they are presented. I was able to get it to work just fine in VBA by going to the 'ScrollArea' in the Properties Window for the sheet in question. The problem is that when I save the file, close and re-open it, the scroll area is right back where it started, with users being able to scroll wherever they want to on the sheet, far beyond where I want them to go. I simply can't understand why it won't stay permanent!

I even tried a couple of VBA code suggestions and they didn't work at all, even after I ran the macro. Any suggestions are much appreciated. Thanks!

 

[mikerickson]

You would put it in a normal module and call it. Done.
If you later decide to add a failsafe by calling it with the Open event, you could add that.

With Sheets("Sheet19")
    Range(.Range("U:U"), .Cells(1, Columns.Count)).EntireColumn.Hidden = True
    Range(.Range("19:19"), .Cells(Rows.Count, 1)).EntireRow.Hidden = True
End With

 

[CRE_finance_guy]

@mikerickson where would I place this code? Sheet 19 is the one in question and the range I want to limit users to not be able to scroll outside of is A1:Q18.

Hi LeopardHawk. Did you ever get this working? I just started a thread on this exact same issue. I'm not sure why such simple code won't work. I keep hoping some VBA master will help us solve this, but so far no one has come up with a solution. I'm starting to think it's some odd glitch in Excel. Please do let me know if you've found a solution. By the way, here's my thread. Sounds like the same issue you're having. VBA Mystery

 

[mikerickson]

As I said above, you would put this in a sub, in a normal module, run it once and be done with it.