Automatically Locking or Unlocking a Cell..?

[Eggspit]

Hi..

I have created a form that requires the user to enter details in selected fields.
I lock the worksheet so the user can simply use the tab key to access through the fields that require data inputting..

Some fields only require data if the information entered in a previous cell is a “Y” (yes) rather then an “N” (no).
Is there a way of tidying up my form, so that a cell would automatically Lock or Unlock itself depending on the criteria of a separate cell..?

Example..
Lock cell A1 if cell A2 = “Y”

Currently I have Conditional Formatting to shade out the cells that do not require data, but the user still has to tab through to bypass the un-required cells.

Any help, would be much appreciated..
Thanks loads,

Egg.

 

[SiddersAtCHEP]

If you use something like the following, you may get what you want. You need to add this to the worksheet code. In the example below, everytime you change a cell to "Y", the cell to the right of the changed cell unlocks. If you change it to any other value, the cell to the right locks.

You could put in an extra check so that this only happens if the activecell.column = 1 for example

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveCell.Value = "Y" Then
ActiveCell.Offset(0, 1).Locked = False
Else
ActiveCell.Offset(0, 1).Locked = True
End If
End Sub