Automatically Locking or Unlocking a Cell..?

Eggspit

New Member
Joined
Oct 23, 2006
Messages
39
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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

SiddersAtCHEP

Board Regular
Joined
Nov 2, 2005
Messages
104
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,850
Messages
5,544,660
Members
410,628
Latest member
mike5
Top