RE: Not allowing users to enter data into certain cells if another cel

Tech-Archive recommends: Fix windows errors by optimizing your registry

You can get away with the data validation thing or you can use code. Here is
some fairly simple code to do it

Private Sub Worksheet_Activate()
Active***.Unprotect
Range("J9:AG100").Locked = True
Active***.Protect
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range

Set rng = Intersect(Target, Range("J7:AG7"))
If Not rng Is Nothing Then
Active***.Unprotect
If Target.Value <> Empty Then
Range(Target.Offset(2, 0), Target.Offset(1002, 0)).Locked = False
End If
Active***.Protect
End If
End Sub

Drop that into the *** and off you go... (right click on the *** tab and
select view code and paste). Bear in mind that the *** is now protected...

HTH

"KimberlyC" wrote:

> Hi,
>
> I would like to NOT allow the user to enter any data into cells
> J9:J1000 if cell J7 is empty and
> K9:K1000 if K7 is empty and
> L9:L1000 if L7 is empty and
> M9:M1000 if M7 is empty and
> N9:N1000 if N7 is empty and
> O9:O1000 if O7 is empty and
> All the way to
> AG9:AG1000 if AG7 is empty
>
> I was thinking I could do this with data valadation..but I could not get
> that to work.
>
> Maybe this can be done with VBA??
>
> Any help is greatly appreciated.
>
> Thanks,
> Kimberly
>
>
>
.

Quantcast