Not allowing users to enter data into certain cells if another cell is empty

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

Data validation should work. What formula did you use?

--
Regards,
Tom Ogilvy

"KimberlyC" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>

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()
ActiveSheet.Unprotect
Range("J9:AG100").Locked = True
ActiveSheet.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
ActiveSheet.Unprotect
If Target.Value <> Empty Then
Range(Target.Offset(2, 0), Target.Offset(1002, 0)).Locked = False
End If
ActiveSheet.Protect
End If
End Sub

Drop that into the sheet and off you go... (right click on the sheet tab and
select view code and paste). Bear in mind that the sheet 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
>
>
>

Kimberly,
Try the following code. Paste it into the Worksheet_Change event of the
sheet that you want this to work in.

If Target.Cells.Count > 1 Then Exit Sub

If Target.Cells(1).Row >= 9 _
And Target.Cells(1).Row <= 1000 _
And Target.Cells(1).Column >= 10 _
And Target.Cells(1).Column <= 33 Then

If Target.Parent.Cells(7, Target.Cells(1).Column).Value = "" Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If


HTH
Alok Joshi


"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
>
>
>

Oops... Change
Range("J9:AG100").Locked = True
to
Range("J9:AG1000").Locked = True

Sorry... :-)


"Jim Thomlinson" wrote:

> 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()
> ActiveSheet.Unprotect
> Range("J9:AG100").Locked = True
> ActiveSheet.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
> ActiveSheet.Unprotect
> If Target.Value <> Empty Then
> Range(Target.Offset(2, 0), Target.Offset(1002, 0)).Locked = False
> End If
> ActiveSheet.Protect
> End If
> End Sub
>
> Drop that into the sheet and off you go... (right click on the sheet tab and
> select view code and paste). Bear in mind that the sheet 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
> >
> >
> >

Tom is going to blow this code apart because it is not foolproof. If anything
is deleted in row 7 the cells below remain unlocked... if you navaigate away
from the sheet and then back all of the cells below 7 are locked... If the
validation thing won't work I will fix that up... It is a very rough start...

;-)

"Jim Thomlinson" wrote:

> 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()
> ActiveSheet.Unprotect
> Range("J9:AG100").Locked = True
> ActiveSheet.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
> ActiveSheet.Unprotect
> If Target.Value <> Empty Then
> Range(Target.Offset(2, 0), Target.Offset(1002, 0)).Locked = False
> End If
> ActiveSheet.Protect
> End If
> End Sub
>
> Drop that into the sheet and off you go... (right click on the sheet tab and
> select view code and paste). Bear in mind that the sheet 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
> >
> >
> >

Very nice... That will work. I would use the intersect method similar to my
original code to determine if the target is within a given range but that is
just a matter of coding style.

"Alok" wrote:

> Kimberly,
> Try the following code. Paste it into the Worksheet_Change event of the
> sheet that you want this to work in.
>
> If Target.Cells.Count > 1 Then Exit Sub
>
> If Target.Cells(1).Row >= 9 _
> And Target.Cells(1).Row <= 1000 _
> And Target.Cells(1).Column >= 10 _
> And Target.Cells(1).Column <= 33 Then
>
> If Target.Parent.Cells(7, Target.Cells(1).Column).Value = "" Then
> Application.EnableEvents = False
> Application.Undo
> Application.EnableEvents = True
> End If
> End If
>
>
> HTH
> Alok Joshi
>
>
> "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
> >
> >
> >

I used this formula in cell J9
=NOT(ISBLANK(J7))

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Data validation should work. What formula did you use?
>
> --
> Regards,
> Tom Ogilvy
>
> "KimberlyC" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> >
>
>

go to the namebox and type in

J9:J1000 and hit enter (this select J9 to J1000 so you can apply the
validation all at once).

then do Data=>Validation and select custom.

Put in the formula

=LEN(TRIM($J$7))<>0 (use the absolute reference for $J$7

** Uncheck the Ignore Blank check box **

click OK.

This worked for me.

--
Regards,
Tom Ogilvy

"KimberlyC" <[email protected]> wrote in message
news:[email protected]...
> I used this formula in cell J9
> =NOT(ISBLANK(J7))
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Data validation should work. What formula did you use?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "KimberlyC" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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
> > >
> > >
> >
> >
>
>

This works very well too!!

Thank you all for your help!


"Alok" <[email protected]> wrote in message
news:[email protected]...
> Kimberly,
> Try the following code. Paste it into the Worksheet_Change event of the
> sheet that you want this to work in.
>
> If Target.Cells.Count > 1 Then Exit Sub
>
> If Target.Cells(1).Row >= 9 _
> And Target.Cells(1).Row <= 1000 _
> And Target.Cells(1).Column >= 10 _
> And Target.Cells(1).Column <= 33 Then
>
> If Target.Parent.Cells(7, Target.Cells(1).Column).Value = "" Then
> Application.EnableEvents = False
> Application.Undo
> Application.EnableEvents = True
> End If
> End If
>
>
> HTH
> Alok Joshi
>
>
> "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
> >
> >
> >

Thanks Tom!

It worked for me too.

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> go to the namebox and type in
>
> J9:J1000 and hit enter (this select J9 to J1000 so you can apply the
> validation all at once).
>
> then do Data=>Validation and select custom.
>
> Put in the formula
>
> =LEN(TRIM($J$7))<>0 (use the absolute reference for $J$7
>
> ** Uncheck the Ignore Blank check box **
>
> click OK.
>
> This worked for me.
>
> --
> Regards,
> Tom Ogilvy
>
> "KimberlyC" <[email protected]> wrote in message
> news:[email protected]...
> > I used this formula in cell J9
> > =NOT(ISBLANK(J7))
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > > Data validation should work. What formula did you use?
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "KimberlyC" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Just be advised that if users disable macros or in xl2002 and later, if
security is set to High, then macros are disabled automatically with no
prompt unless your certification has been accepted as a trusted source -
then the change event won't work.

--
Regards,
Tom Ogilvy

"KimberlyC" <[email protected]> wrote in message
news:[email protected]...
> This works very well too!!
>
> Thank you all for your help!
>
>
> "Alok" <[email protected]> wrote in message
> news:[email protected]...
> > Kimberly,
> > Try the following code. Paste it into the Worksheet_Change event of the
> > sheet that you want this to work in.
> >
> > If Target.Cells.Count > 1 Then Exit Sub
> >
> > If Target.Cells(1).Row >= 9 _
> > And Target.Cells(1).Row <= 1000 _
> > And Target.Cells(1).Column >= 10 _
> > And Target.Cells(1).Column <= 33 Then
> >
> > If Target.Parent.Cells(7, Target.Cells(1).Column).Value = ""
Then
> > Application.EnableEvents = False
> > Application.Undo
> > Application.EnableEvents = True
> > End If
> > End If
> >
> >
> > HTH
> > Alok Joshi
> >
> >
> > "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
> > >
> > >
> > >
>
>

One last thing!!

Is there a way to make this code select cell 7 if the users tries to enter
data into cells 9:1000
For example.. If J7 is empty and user tries to enter data in J9:J1000.. it
will not let them..(as this code does)...and then I would like have a
message pop up stating they must enter data in cell 7...and have the code
select J7.

Thanks in advance for your help!!


"KimberlyC" <[email protected]> wrote in message
news:[email protected]...
> This works very well too!!
>
> Thank you all for your help!
>
>
> "Alok" <[email protected]> wrote in message
> news:[email protected]...
> > Kimberly,
> > Try the following code. Paste it into the Worksheet_Change event of the
> > sheet that you want this to work in.
> >
> > If Target.Cells.Count > 1 Then Exit Sub
> >
> > If Target.Cells(1).Row >= 9 _
> > And Target.Cells(1).Row <= 1000 _
> > And Target.Cells(1).Column >= 10 _
> > And Target.Cells(1).Column <= 33 Then
> >
> > If Target.Parent.Cells(7, Target.Cells(1).Column).Value = ""
Then
> > Application.EnableEvents = False
> > Application.Undo
> > Application.EnableEvents = True
> > End If
> > End If
> >
> >
> > HTH
> > Alok Joshi
> >
> >
> > "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
> > >
> > >
> > >
>
>