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!

 

[Jaafar Tribak]

That particular setting is not permanent. Which VBA code suggestiosn have you tried ?

Does this not work for you ?

Private Sub Workbook_Open()
    Sheet1.ScrollArea = "A1:U100"
End Sub

 

[Gokhan Aycan]

Not the most elegant but here it goes Also, locks a lot of things, so not a real solution either.

Example won't scroll past row 30 (which may be partially visible).

Code is on a standard module.

Option Explicit

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Public Sub test_scroll()

    While 1
  
        DoEvents
        Sleep (50)
        If ActiveWindow.VisibleRange.Row + ActiveWindow.VisibleRange.Rows.Count - 1 > 30 Then ActiveWindow.ScrollRow = 30 - ActiveWindow.VisibleRange.Rows.Count + 1
  
    Wend

End Sub

 

[leopardhawk]

@Jaafar Tribak hi Jaafar, thank you for responding. Yes, earlier I did try putting that code in the ThisWorkbook object and I can still scroll well past the defined parameters. Very frustrating for me. Hard to believe that this is such a difficult thing for Excel...

This is what I used:

Private Sub Workbook_Open()
    Sheet19.ScrollArea = "A1:Q18"
End Sub

I also tried this:

Private Sub Workbook_Open()
    Sheets("Sheet19").ScrollArea = "A1:Q18"
End Sub

 

[leopardhawk]

@Gokhan Aycan Hi! That is probably well outside my capabilities to understand but I appreciate you taking the time to post it. I am a bit of a newbie to all this VBA code so it is unlikely that I could make use of your code. Really, I am looking for the simplest solution! Thanks!

 

[Jaafar Tribak]

@Jaafar Tribak hi Jaafar, thank you for responding. Yes, earlier I did try putting that code in the ThisWorkbook object and I can still scroll well past the defined parameters. Very frustrating for me. Hard to believe that this is such a difficult thing for Excel...

This is what I used:

Private Sub Workbook_Open()
    Sheet19.ScrollArea = "A1:Q18"
End Sub

I also tried this:

Private Sub Workbook_Open()
    Sheets("Sheet19").ScrollArea = "A1:Q18"
End Sub

That is strange!
Are the application events enabled ?

Also, what happens if you run this code not from the workbook open event but from a routine in a standard module ?
something like this :

Sub test()
    Sheets("Sheet1").ScrollArea = "A1:U100"
    MsgBox Sheets("Sheet1").ScrollArea
End Sub

Does it now work ? and do you get the scrollarea range displayed in the MsgBox ?

 

[mikerickson]

Perhaps this, which persists but can be undone by the user, if it annoys them.

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

 

[leopardhawk]

@Jaafar Tribak no, application events are not enabled. I did as you suggested after changing your code to fit my sheet and it's not limiting the range of scrolling and no msgbox.

Sub test()
    Sheets("Sheet19").ScrollArea = "A1:Q18"
    MsgBox Sheets("Sheet19").ScrollArea
End Sub

 

[leopardhawk]

@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.

 

[GWteB]

Just an observation and it might be out of order, but with visible ribbon, formula bar, headings, sheet tabs and 100% zoom, cell AC40 is visible on my rather small screen.
A scroll area limited to A1:Q18 (which @leopardhawk is attempting and btw should work; both scrolling or selecting a cell outside that area is supposed to be blocked) doesn't change that.