0

I have a list of names in a Sheet Column A.

 A
---
Robby
Harry
Nick

In any cell when I enter a name and click on other cell. If the entered name not present in the list then the focus shouldn't move to other cell. I shouldn't be able to select other cell or move the cursor.The cursor should only be focused to the entered cell. Cursor should be locked until I won't enter name given in list. Once I enter name in given list I should be able to move cursor to other cell.

So how can I lock the cursor to specific cell and focus to only that specific cell using VBA code in Excel.

2
  • 1
    Welcome to Super User. We are not a script writing service. We expect users to tell us what they have tried so far (including any scripts they are using) and where they're stuck so that we can help with specific problems. Questions that only ask for scripts are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?.
    – DavidPostill
    Dec 1, 2015 at 16:36
  • possible duplicate but VTC unclear. Dec 2, 2015 at 12:00

1 Answer 1

1

Place the following event macros in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("A:A")) Is Nothing Then
      v = Target.Value
      Dim r As Range
      If Range("A:A").Find(what:=v, after:=Range("A1")) Is Nothing Then
         Target.Select
         Stuck = True
         Set rStuck = Target
      Else
         Stuck = False
      End If
   End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Stuck Then rStuck.Select
End Sub

Place the following in a standard module:

Public Stuck As Boolean
Public rStuck As Range

Note that this code will allow you to make changes to column A, but apply restrictions to changes in any other column.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .