|  | Customize Sheet, Limit Area | | |
| | | Josh |  |
|  Posted: Sat Jun 28, 2008 2:05 am Post subject: Customize Sheet, Limit Area |  |
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 | |
| | Posted: Sat Jun 28, 2008 2:05 am Post subject: Re: Customize Sheet, Limit Area | |
| |  | |
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
On Fri, 27 Jun 2008 19:05:13 -0700 (PDT), Josh <jjreicher@gmail.com> wrote:
| Quote: | 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? |
|
| |
| | | Josh | |
| | Posted: Sat Jun 28, 2008 6:17 am Post subject: Re: Customize Sheet, Limit Area | |
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 | |
| | Posted: Sat Jun 28, 2008 12:40 pm Post subject: Re: Customize Sheet, Limit Area | |
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
"Josh" wrote:
| Quote: | 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?
|
|
| |
| | | Josh | |
| | Posted: Sat Jun 28, 2008 4:13 pm Post subject: Re: Customize Sheet, Limit Area | |
| | | |
On Jun 28, 7:40 am, ShaneDevenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
| Quote: | 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
"Josh" wrote:
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?- Hide quoted text -
- 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 | |
| | Posted: Sun Jun 29, 2008 12:37 am Post subject: Re: Customize Sheet, Limit Area | |
| | | |
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.
--
Cheers,
Shane Devenshire
"Josh" wrote:
| Quote: | On Jun 28, 7:40 am, ShaneDevenshire
ShaneDevensh...@discussions.microsoft.com> wrote:
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
"Josh" wrote:
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?- Hide quoted text -
- 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!
|
|
| |
|
|
