Restricting access only to specific cells on a spreadsheet

aparna

New Member
Joined
Nov 4, 2005
Messages
16
I have a spreadsheet with three cells for data entry. Is there any way I can make the cursor move only between these cells. Data Validation functionality did not help me since it doesnt stop me from placing the cursor anywhere in a protected sheet.
Ideally I would like the cursor to move only between the three designated cells in a spreadsheet that I have created.
Any help on this would be highly appreciated.
Aparna
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
When you prtoect your sheet there is an option that says "Select locked cells" uncheck it
 
Upvote 0
... I think that you have to be using Excel 2002 (or is it 2000?) or later to be able to do this.
 
Upvote 0
I am using Excel 2000 - doesnt show me any such thing as "select locked cells" when i protect a sheet
-aparna
 
Upvote 0
aparna said:
I am using Excel 2000 - doesnt show me any such thing as "select locked cells" when i protect a sheet
-aparna
... then I think a VBA solution will be required.
 
Upvote 0
If you use the TAB key then the cursor will only jump around the unlocked cells. Otherwise, you could use a worksheet_selectionchange event to make the cursor jump to one of the unocked cells, or make a mwessage box appear eg
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$a$1" And _
 Target.Address <> "$d$6" And _
  Target.Address <> "$f$8" Then
 MsgBox ("Please select only cells .... or use the TAB key")
 Application.EnableEvents = False
  Cells(1, 1).Select
   Application.EnableEvents = True
  End If
End Sub
 
Upvote 0
Hello, aparna
Welcome to the Board !
try this
you don't need to specify anything
see comments within code
Code:
Option Explicit

Public PrevSel As Range 'previous selection

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Erik Van Geit
'051115
'workaround for versions which don't have "disable selecting locked cells"
'ActiveSheet.EnableSelection = xlUnlockedCells
'can be used on unprotected sheets to
'code can slow down when large used range and selecting large range

Dim rng As Range
Dim cell As Range
    For Each cell In Intersect(Target, Me.UsedRange)
    If cell.Locked = False Then
    If rng Is Nothing Then Set rng = cell Else Set rng = Union(rng, cell)
    End If
    Next cell

If rng Is Nothing Then
    If PrevSel Is Nothing Then
        For Each cell In Me.UsedRange
            If cell.Locked = False Then
            cell.Select
            Exit For
            End If
        Next cell
    Else: PrevSel.Select
    End If
Else
rng.Select
End If
Set PrevSel = Selection
End Sub
kind regards,
Erik
 
Upvote 0
this one will be much quicker
take care of correct "Allowed range" each time you change the locked-property
Code:
Option Explicit

Public PrevSel As Range 'previous selection

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Erik Van Geit
'051115
'only allow to select certain cells

Dim AllowedRng As Range
Dim rng As Range
Dim cell As Range

Set AllowedRng = Range("A10,B2,C14")

    For Each cell In AllowedRng
    If Not Intersect(cell, Selection) Is Nothing Then
    If rng Is Nothing Then Set rng = cell Else Set rng = Union(rng, cell)
    End If
    Next cell

If rng Is Nothing Then
    If PrevSel Is Nothing Then
    AllowedRng(1).Select
    Else: PrevSel.Select
    End If
Else
rng.Select
End If
Set PrevSel = Selection
End Sub
best regards,
Erik
 
Upvote 0
Thanks so much Erik...

The second script that you suggested is simply wonderful. It's just the stuff that I wanted. You are simply amazing.

Also, tell me how do I master VBA - is there a book that you could suggest or a training program.

Warm regards and best wishes,

Aparna
 
Upvote 0
Also, tell me how do I master VBA - is there a book that you could suggest or a training program.
my method was "trial and error" and board-surfing
so you will not find the right person in me to give good advise on this
here on the board you can find some books
see also the recommended add-ins and links
and these
(sorry to those which are not in this list)
http://www.dicks-blog.com
http://support.microsoft.com/default.aspx?pr=kbinfo
http://xcelfiles.homestead.com
http://j-walk.com
http://peltiertech.com
http://www.danielklann.com
http://www.cpearson.com
http://www.tmehta.

God bless you!
Erik
 
Upvote 0

Forum statistics

Threads
1,226,588
Messages
6,191,889
Members
453,684
Latest member
Gretchenhines

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top