Limit the size of the spreadsheet

[Lily]

I want to set up the size of my spreadsheet to a limit number of rows and
columns, but I don't know how.

I found the post pasted below, which was helpful but if I close my file and
open it again, all the rows and colums are available again

==========
http://www.mrexcel.com/archive/VBA/6459.html

......(this example assumes you're working with sheet1.)
Open the VB Editor (Alt+F11) and click View, Project Explorer. The Click
View, Properties.
Click the relevant worksheet in the explorer (you may have to expand the
tree). In the Properties window change the ScrollArea to $1:$365
This will prevent the user from scrolling past the 365th row.
==========================================

Hope someone can help.

 

[Mike]

You could put this into the Thisworkbook module
Private Sub Workbook_Open()
For Each sh In Worksheets
With sh
.ScrollArea = "$1:$365"
End With
Next
End Sub

 

[Billns]

I want to set up the size of my spreadsheet to a limit number of rows and
columns, but I don't know how.

I found the post pasted below, which was helpful but if I close my file and
open it again, all the rows and colums are available again

==========
http://www.mrexcel.com/archive/VBA/6459.html

.....(this example assumes you're working with sheet1.)
Open the VB Editor (Alt+F11) and click View, Project Explorer. The Click
View, Properties.
Click the relevant worksheet in the explorer (you may have to expand the
tree). In the Properties window change the ScrollArea to $1:$365
This will prevent the user from scrolling past the 365th row.
==========================================

Hope someone can help.

Quattro Pro has had this feature for years. You can set up a 1 by 1
worksheet. Why you might want this size is something else.

Bill

 

[Roger Govier]

Hi Lily

Mike has given you the solution of how to make scroll area work each
time you open the workbook.

Al alternative, would be just to Hide the column / Rows.
If you only wanted the sheet to be 13 columns by 50 rows for example
Select column N>Control+Shift+Right arrow to take you tot he last
column>Right click>Hide columns
Select row 51>Control+Shift+Arrow down to take you to the last row>Right
click>Hide rows

The hidden rows and columns will be "greyed out", but the user can still
scroll the sheet up and down, but they won't see any gridlines, nor be
able to enter any data.

If you need to display the columns / rows again, click on the Sheet
button (above row 1 and to left of column A).
This will select the whole sheet>Format>Columns>Unhide