Restricting cells

  • Thread starter Thread starter Noctos
  • Start date Start date
N

Noctos

is it possible to preventing a user select any cells on the workbook
apart from a2 and b3 and preventing them from scrolling down the page.
And maybe if it is possible for the use to get from cell to cell by
just pressing tab like we do on the net. thanks if you can solve this
problem.
 
Noctos

You can unlock the cell the user is to have access to and
protect the worksheet. The user can then tab between the
unprotected cells. The can still use the Arrow keys are
whatever to scroll down the sheet.

You might want to place sensitive data on another sheet
and hide it.

I don't know about hiding scroll bars - maybe the experts
do.

Regards
Peter
 
yea thanks peter as for scrolling down the code is something like this

activesheets.ScrollArea = "$a$1:$m$40"

but i don't know where to place it so it has effect on all sheets. i've
tried various places but they are not working.
 
Noctos,

Yes you can...

First of all Rightclick on cells a2 and b3, choose Format Cells. Goto the
Protection tab and unselect the option Locked.

Now, to make sure the user cannot scroll off the page, you should hide all
the rows and columns that the user is not
supposed to see (no matter if they are used or not). You can do that by
clicking on the rowlabel of the top row, push the buttons [End] [Arrow
down], rightclick on one of the selected rowlabels and choose the option
Hide. After this click on the columnlabel of the most left row you want to
hide, push the buttons [End] [Arrow right], rightclick on one of the
selected columnlabels and choose the option Hide.

Now Protect your worksheet by selecting Tools / Protection / Protect
Sheet... (you can sepecify a password if you want, not obligated). Now your
worksheet should work as intended.

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl
 
Private Sub Workbook_Open()
With Worksheets("Sheet1")
.Activate
.Unprotect
.Range("A1").Select
.Cells.Locked = True
.Range("A2,B3").Locked = False
.ScrollArea = ActiveWindow.VisibleRange.Address
.Protect
.EnableSelection = xlUnlockedCells
End With
End Sub

Put in the ThisWorkbook module of your workbook.
 
If you want to restrict for all worksheets in the workbook

Private Sub Workbook_Open()
Dim sh as Worksheet
for each sh in ThisWorkbook.Worksheets
With sh
.Activate
.Unprotect
.Range("A1").Select
.Cells.Locked = True
.Range("A2,B3").Locked = False
.ScrollArea = ActiveWindow.VisibleRange.Address
.Protect
.EnableSelection = xlUnlockedCells
End With
Next
End Sub

--
Regards,
Tom Ogilvy


Tom Ogilvy said:
Private Sub Workbook_Open()
With Worksheets("Sheet1")
.Activate
.Unprotect
.Range("A1").Select
.Cells.Locked = True
.Range("A2,B3").Locked = False
.ScrollArea = ActiveWindow.VisibleRange.Address
.Protect
.EnableSelection = xlUnlockedCells
End With
End Sub

Put in the ThisWorkbook module of your workbook.
Click to expand...
 
Back
Top