Customize Sheet, Limit Area

[Josh]

I am looking for a way to restrict a sheet or area within a sheet in
such a way that any editing is permitted within the given area, but
the size of the area itself cannot be altered. A "chart" sheet is sort
of on the right track, as the total area of the sheet is set but no
restrictions are forced on changes within that area (except that it is
limited to charts only).

Ideas?

 

[Gord Dibben]

Lock all cells but the block/area you want users to access.

Hide all the locked columns and rows.

Protect the sheet and allow users to "select unlocked cells" only.

Alternative...............without protection set the scrollarea to a limited
area.

Since the scrollarea method does not stick between sessions you will have to
reset it each time you open the workbook.

You may wish to place the code into a WorkBook_Open Sub in ThisWorkbook module
and specify which worksheet if only one sheet required.

Adjust the sheetname and range to suit.

Private Sub WorkBook_Open()
Sheets("YourSheet").ScrollArea = "A1:M67"
End Sub

Or also in the Thisworkbook module to limit scrollarea on all sheets.

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


Gord Dibben MS Excel MVP

 

[Josh]

Thanks for the ideas.

Unfortunately, this does not quite solve the problem I have. Limiting
users to a specific range of cells (using either of the methods you
describe) does not prevent users from increasing the heights and
widths of the cells being used. This means the total area can still
easily be changed.

My problem is that I need the area (as in size, not region) to be
limited.

Any ideas?

 

[ShaneDevenshire]

Hi Josh,

On my computer if you Unlock the cells in the range you want the user to
move through but not change row height and column width and then turn on
Sheet protections you can not change the row or column height. What version
of Excel are you using?

You unlock the cells you want them to move through by choosing Format,
Cells, Protection, and unchecking Locked. Then you choose Tools, Protection,
Protect Sheet, OK.

 

[Josh]

Hi Josh,

On my computer if you Unlock the cells in the range you want the user to
move through but not change row height and column width and then turn on
Sheet protections you can not change the row or column height.  What version
of Excel are you using?

You unlock the cells you want them to move through by choosing Format,
Cells, Protection, and unchecking Locked.  Then you choose Tools, Protection,
Protect Sheet, OK.

--
Cheers,
Shane Devenshire








- Show quoted text -

Well, I'm getting there. Still a few issues though.

With this method, it seems that merging cells is not allowed
(potentially problematic) and inserted objects cannot be moved (big
problem). How can I solve the problems?

Thanks!

 

[ShaneDevenshire]

When you choose Tools, Protect Sheet the dialog box allows you to turn on 1.
Select unlocked cells but leave Select locked cells unchecked. 2. You can
check Edit objects which allows the user to insert and move objects. 3. You
can merge cells only when the sheet is unprotected. However, you could write
code that would let the user do this even when the spreadsheet is selected:


You need to protect the sheet via code:
Sub ProtectMe()
ActiveSheet.Protect userinterfaceonly:=True
End Sub

Sub MergeAndCenter()
With Selection
.HorizontalAlignment = xlCenter
.MergeCells = True
End With
End Sub

Assign a shortcut key such as Ctrl+M on the spreadsheet side by choosing
Tools, Macro, Macros, selecting the MergeAndCenter macro and clicking Options.

Run the ProtectMe macro when you want to protect the spreadsheet.