Protecting a cell based on another cell

[KimberlyC]

Hi,

For the active worksheet.... I need to do the following if possible....
If cells F10:F51 have "E" entered, then I need the corresponding cells in
G10:G51 to become protected or not allow anything to be entered into those
cells.
For Example:
If a user enterted "E" in F20, then G20 would not allow the user to entered
anything into it..

The sheet is already protected...and G10:G51 are not protected cells...

I'm not sure if this is possible...but if it is.. I 'm guessing it would
have to be done thru code.


Any help would be greatly appreciated..
Thanks in advance,
Kimberly

[Greg Wilson]

I suggest setting the EnableSelection property for the worksheet involved to
xlUnlockedCells so that the user can't even click on the locked cells in
column G. Otherwise, when they try to enter something, they will get an
annoying message saying "The cell you are trying to change is protected...".
I would suggest also toggling the cell interior colour from, say gray when
locked to white when unlocked, or similar. You might also want to consider,
instead of relying on the user to type in the "E", rigging it so that
clicking on the cells in column F toggles the "E", or alternatively, relying
on option buttons or some other method.

Instructions:
1) First unlock all cells on the worksheet that you want the user to be able
to select.
2) Then paste the following into the "ThisWorkbook" module. Note that it is
asummed that the worksheet is named "Data Entry".

Private Sub Workbook_Open()
With Sheets("Data Entry")
.Unprotect
.EnableSelection = xlUnlockedCells
.Protect UserInterfaceOnly:=True
End With
End Sub

3) Now paste the following into the worksheet's code module - e.g.
"Sheet1(Data Entry)":

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("F10:F51")
If Intersect(Target, rng) Is Nothing Then Exit Sub
Target(1, 2).Locked = (Trim(UCase(Target.Value)) = "E")
End Sub

4) Now close and reopen the workbook and try it out.

Regards,
Greg

"KimberlyC" wrote:

>
> Hi,
>
> For the active worksheet.... I need to do the following if possible....
> If cells F10:F51 have "E" entered, then I need the corresponding cells in
> G10:G51 to become protected or not allow anything to be entered into those
> cells.
> For Example:
> If a user enterted "E" in F20, then G20 would not allow the user to entered
> anything into it..
>
> The sheet is already protected...and G10:G51 are not protected cells...
>
> I'm not sure if this is possible...but if it is.. I 'm guessing it would
> have to be done thru code.
>
>
> Any help would be greatly appreciated..
> Thanks in advance,
> Kimberly
>
>
>

[Greg Wilson]

Further to my post, to avoid possible complications, you should replace the
line:
"If Intersect(Target, rng) Is Nothing Then Exit Sub"
With:
"If Intersect(Target, rng) Is Nothing Or _
Target.Count > 1 Then Exit Sub"

Regards,
Greg

"Greg Wilson" wrote:

> I suggest setting the EnableSelection property for the worksheet involved to
> xlUnlockedCells so that the user can't even click on the locked cells in
> column G. Otherwise, when they try to enter something, they will get an
> annoying message saying "The cell you are trying to change is protected...".
> I would suggest also toggling the cell interior colour from, say gray when
> locked to white when unlocked, or similar. You might also want to consider,
> instead of relying on the user to type in the "E", rigging it so that
> clicking on the cells in column F toggles the "E", or alternatively, relying
> on option buttons or some other method.
>
> Instructions:
> 1) First unlock all cells on the worksheet that you want the user to be able
> to select.
> 2) Then paste the following into the "ThisWorkbook" module. Note that it is
> asummed that the worksheet is named "Data Entry".
>
> Private Sub Workbook_Open()
> With Sheets("Data Entry")
> .Unprotect
> .EnableSelection = xlUnlockedCells
> .Protect UserInterfaceOnly:=True
> End With
> End Sub
>
> 3) Now paste the following into the worksheet's code module - e.g.
> "Sheet1(Data Entry)":
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rng As Range
> Set rng = Range("F10:F51")
> If Intersect(Target, rng) Is Nothing Then Exit Sub
> Target(1, 2).Locked = (Trim(UCase(Target.Value)) = "E")
> End Sub
>
> 4) Now close and reopen the workbook and try it out.
>
> Regards,
> Greg
>
> "KimberlyC" wrote:
>
> >
> > Hi,
> >
> > For the active worksheet.... I need to do the following if possible....
> > If cells F10:F51 have "E" entered, then I need the corresponding cells in
> > G10:G51 to become protected or not allow anything to be entered into those
> > cells.
> > For Example:
> > If a user enterted "E" in F20, then G20 would not allow the user to entered
> > anything into it..
> >
> > The sheet is already protected...and G10:G51 are not protected cells...
> >
> > I'm not sure if this is possible...but if it is.. I 'm guessing it would
> > have to be done thru code.
> >
> >
> > Any help would be greatly appreciated..
> > Thanks in advance,
> > Kimberly
> >
> >
> >