Where's the bottom

  • Thread starter Thread starter monagan
  • Start date Start date
M

monagan

I need to know how to set a definate end to a spread sheet, so users d
not scroll past a certain ro
 
The definite end to an XL spread sheet is Row 65536.

You can prevent scrolling past, say, Row 100 by putting this code in the
ThisWorkbook module of your workbook (right-click the workbook title bar
and choose View Code):

Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "1:100"
End Sub

However, this will not prevent the user from entering A101 in the Name
box on the formula bar and making an entry in that cell. To prevent
that, you'll need an event macro in the worksheet code module
(right-click the worksheet tab and choose View Code):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rTemp As Range
Application.EnableEvents = False
With Selection
Set rTemp = Intersect(.Cells, Range("1:100"))
If Not rTemp Is Nothing Then
rTemp.Select
Else
Cells(100, .Item(1).Column).Resize( _
1, .Columns.Count).Select
End If
End With
Application.EnableEvents = True
End Sub
 
Monagan,

You can hide all the rows and colums past your area. To do that, select the
first row to be hidden (click in the row header), select from there down
(Ctrl-Shift-DownArrow), right click in the selected area and choose Hide.
Same for columns if you wish.

If there is stuff beyond a certain row you wish to be able to see, but not
scroll too, then you have to set the ScrollArea, which requires a macro to
run each time the workbook is opened. This also limits selection, as well
as scrolling.
 
Back
Top