Solved: worksheet scrolling

[drawworkhome]

hi all,
if i wanted the user to be able to only scroll thru a range (say c5:f15) of a particular worksheet, is there a method to do that? i am using 2007 and 2003 vba. if there is a method, are there any particular negatives to doing so?
thank you.

[GTO]

Hi there,

As that's a pretty small range, when you say "scroll", do you actually mean scroll with the scrollbar, or limit the user to selecting cells in the aforementioned range?

Mark

[drawworkhome]

the range size is something i just made up.
looking at my workbook (on a friday night......) it would really be b21:j34
i would be interested in info on both of your queries. if i had to pick, i would say limit the selection to only that range.
erik

[GTO]

Hi Erik,

In a blank/new workbook, place this in the ThisWorkbook Module.
[vba]
Option Explicit

Private Sub Workbook_Open()
Sheet1.ScrollArea = "A1:Z100"
With Sheet2
With .Range("B2:J20")
.Interior.ColorIndex = 6
.Locked = False
End With
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
.EnableSelection = xlUnlockedCells
End With
End Sub
[/vba]

After saving and closing the wb, when you reopen it, you will only be able to scroll in the designated area on Sheet1, and Sheet2 will only allow the user to select unprotected cells.

Mark

[jolivanes]

Would any of these do?

If you want it to apply to a specific worksheet

Private Sub Worksheet_Activate() 
    Me.ScrollArea = "A1:BS46" 
End Sub

If you're always opening it at the sheet you want to restrict the scroll area on use (in the ThisWorkbook module)

Option Explicit 
Private Sub Workbook_Open() 
    ActiveSheet.ScrollArea = "A1:BS46" 
End Sub

if you want the restricted area to apply to every worksheet use (in the ThisWorkbook module)

Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
    ActiveSheet.ScrollArea = "A1:BS46" 
End Sub

[drawworkhome]

thank you all for the info.
erik

[mdmackillop]

For entering data in restricted columns I use this.
Select cols 1-3 and run SetScroll. To restore, select a single cell and run SetScroll

[vba]
Sub SetScroll()
If Selection.Cells.Count = 1 Then
ActiveSheet.ScrollArea = Cells.Address
Application.MoveAfterReturnDirection = xlDown
Else
ActiveSheet.ScrollArea = Selection.Address
Application.MoveAfterReturnDirection = xlToRight
End If
End Sub
[/vba]

[arkusM]

With out using Code you can just set the properties of the sheet using VBA editor set the .ScrollArea manually to the b21:j34. If you don't have any other code on the workbook, and it will not trigger a Macro Warning.
Old School, hard coded and not elegant but an option.

[drawworkhome]

wow, i like the setscroll sub!
thanks again for all your help.
erik