Lock Cell if another cell contains data
 

Say we have worksheets sa and sb. In worksheet code for sa enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Call routine
End Sub


In a standard module:

Sub routine()
Dim w As Worksheet
Set w = Worksheets("sb")
For Each r In w.UsedRange
r.Locked = r.HasFormula
Next
End Sub
--
Gary's Student


"adodson" wrote:

I would like to lock formulas in cells on worksheet B if data is entered in
worksheet A. Otherwise, the user would need to enter data on top of the
formula.

How would I go about this?

I tried looking through other posts and couldn't find this... so thanks for
any assistance you may offer.

Lock Cell if another cell contains data
 

This might be faster:

Sub routine()
Dim rng as Range
Dim w As Worksheet
Set w = Worksheets("sb")
'w.Unprotect
On Error Resume Next
set rng = w.UsedRange.SpecialCells(xlFormulas)
On Error goto 0
if not rng is nothing then
rng.Locked = True
End if
'w.Protect
End Sub

that said, I would say the OP needs to provide a better explanation of what
he wants, because his explanation doesn't make a whole lot of sense to me.
If the formulas are in worksheet B, why would entries in worksheet A "need
to enter data on top of the formula." ?


--
Regards,
Tom Ogilvy


"Gary''s Student" wrote in message
...
Say we have worksheets sa and sb. In worksheet code for sa enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Call routine
End Sub


In a standard module:

Sub routine()
Dim w As Worksheet
Set w = Worksheets("sb")
For Each r In w.UsedRange
r.Locked = r.HasFormula
Next
End Sub
--
Gary's Student


"adodson" wrote:

I would like to lock formulas in cells on worksheet B if data is entered
in
worksheet A. Otherwise, the user would need to enter data on top of the
formula.

How would I go about this?

I tried looking through other posts and couldn't find this... so thanks
for
any assistance you may offer.

Lock Cell if another cell contains data
 

If they enter stuff in space A, I want it to flow through to space B.
However, if nothing is entered in space A, I don't want space B to be locked.
They have an option of whether they want to utilize space A or provide their
own version of space A separate.

Thank you for the responses.

"Tom Ogilvy" wrote:

This might be faster:

Sub routine()
Dim rng as Range
Dim w As Worksheet
Set w = Worksheets("sb")
'w.Unprotect
On Error Resume Next
set rng = w.UsedRange.SpecialCells(xlFormulas)
On Error goto 0
if not rng is nothing then
rng.Locked = True
End if
'w.Protect
End Sub

that said, I would say the OP needs to provide a better explanation of what
he wants, because his explanation doesn't make a whole lot of sense to me.
If the formulas are in worksheet B, why would entries in worksheet A "need
to enter data on top of the formula." ?


--
Regards,
Tom Ogilvy


"Gary''s Student" wrote in message
...
Say we have worksheets sa and sb. In worksheet code for sa enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Call routine
End Sub


In a standard module:

Sub routine()
Dim w As Worksheet
Set w = Worksheets("sb")
For Each r In w.UsedRange
r.Locked = r.HasFormula
Next
End Sub
--
Gary's Student


"adodson" wrote:

I would like to lock formulas in cells on worksheet B if data is entered
in
worksheet A. Otherwise, the user would need to enter data on top of the
formula.

How would I go about this?

I tried looking through other posts and couldn't find this... so thanks
for
any assistance you may offer.

Lock Cell if another cell contains data
 

If Not intersect(Target,Range("SpaceA")) is nothing then
'Range("spaceB").Parent.UnProtect
if application.Counta(range("SpaceA")) 0 then
Range("SpaceB").Locked = True
else
Range("SpaceB").Locked = False
End if
'Range("spaceB").Parent.Protect
end if

--
Regards,
Tom Ogilvy



"adodson" wrote in message
...
If they enter stuff in space A, I want it to flow through to space B.
However, if nothing is entered in space A, I don't want space B to be
locked.
They have an option of whether they want to utilize space A or provide
their
own version of space A separate.

Thank you for the responses.

"Tom Ogilvy" wrote:

This might be faster:

Sub routine()
Dim rng as Range
Dim w As Worksheet
Set w = Worksheets("sb")
'w.Unprotect
On Error Resume Next
set rng = w.UsedRange.SpecialCells(xlFormulas)
On Error goto 0
if not rng is nothing then
rng.Locked = True
End if
'w.Protect
End Sub

that said, I would say the OP needs to provide a better explanation of
what
he wants, because his explanation doesn't make a whole lot of sense to
me.
If the formulas are in worksheet B, why would entries in worksheet A
"need
to enter data on top of the formula." ?


--
Regards,
Tom Ogilvy


"Gary''s Student" wrote in
message
...
Say we have worksheets sa and sb. In worksheet code for sa enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Call routine
End Sub


In a standard module:

Sub routine()
Dim w As Worksheet
Set w = Worksheets("sb")
For Each r In w.UsedRange
r.Locked = r.HasFormula
Next
End Sub
--
Gary's Student


"adodson" wrote:

I would like to lock formulas in cells on worksheet B if data is
entered
in
worksheet A. Otherwise, the user would need to enter data on top of
the
formula.

How would I go about this?

I tried looking through other posts and couldn't find this... so
thanks
for
any assistance you may offer.

Lock Cell if another cell contains data
 

Remember this:

Private Sub Worksheet_Change(ByVal Target As Range)
Call routine
End Sub


so:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not intersect(Target,Range("SpaceA")) is nothing then
'Range("spaceB").Parent.UnProtect
if application.Counta(range("SpaceA")) 0 then
Range("SpaceB").Locked = True
else
Range("SpaceB").Locked = False
End if
'Range("spaceB").Parent.Protect
end if
End Sub

You see, Space A and Space B mean nothing to me.

In my sample code, I assumed they were two named ranges. This code goes in
the sheet where you want the code to operate and where SpaceA and SpaceB
would be defined. Unfortunately, programming required knowledge and when
you don't explain thinks, people have to guess and make assumptions to
illustrate an approach.

If space A means a worksheet and space B means a worksheet then

This would go in the Sheet module of the sheet named SpaceA

Private Sub Worksheet_Change(ByVal Target As Range)
If Not intersect(Target,Worksheets("SpaceA").Range("A1:A1 0")) is nothing
then
Worksheets("spaceB").UnProtect
if application.Counta(worksheets("SpaceA").Range("A1: A10")) 0 then
Worksheets("SpaceB").Range("A1:A10").Locked = True
else
Worksheets("SpaceB").Range("A1:A10").Locked = False
End if
Worksheets("spaceB").Protect
end if
End Sub

--
Regards,
Tom Ogilvy


"adodson" wrote in message
...
Ok, I found how to refer to a worksheet/cell in the VB help files, but I
can't find anything about target or why it might be causing the error?

"adodson" wrote:

Forgive my inexperience, but when I put this code at the top of my edit
macro
area, and replace "space a" and "space b" with the names of the
worksheet, I
receive a compile error highlighting "Target" Any suggestions as to what
I'm
doing incorrect?

Also, how would I modify the code to check a specific cell or range of
cells
within "Space A" to block specific cells in "Space B"?

Thanks again for your assistance.

"Tom Ogilvy" wrote:

If Not intersect(Target,Range("SpaceA")) is nothing then
'Range("spaceB").Parent.UnProtect
if application.Counta(range("SpaceA")) 0 then
Range("SpaceB").Locked = True
else
Range("SpaceB").Locked = False
End if
'Range("spaceB").Parent.Protect
end if

--
Regards,
Tom Ogilvy



"adodson" wrote in message
...
If they enter stuff in space A, I want it to flow through to space B.
However, if nothing is entered in space A, I don't want space B to be
locked.
They have an option of whether they want to utilize space A or
provide
their
own version of space A separate.

Thank you for the responses.

"Tom Ogilvy" wrote:

This might be faster:

Sub routine()
Dim rng as Range
Dim w As Worksheet
Set w = Worksheets("sb")
'w.Unprotect
On Error Resume Next
set rng = w.UsedRange.SpecialCells(xlFormulas)
On Error goto 0
if not rng is nothing then
rng.Locked = True
End if
'w.Protect
End Sub

that said, I would say the OP needs to provide a better explanation
of
what
he wants, because his explanation doesn't make a whole lot of sense
to
me.
If the formulas are in worksheet B, why would entries in worksheet A
"need
to enter data on top of the formula." ?


--
Regards,
Tom Ogilvy


"Gary''s Student" wrote in
message
...
Say we have worksheets sa and sb. In worksheet code for sa enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Call routine
End Sub


In a standard module:

Sub routine()
Dim w As Worksheet
Set w = Worksheets("sb")
For Each r In w.UsedRange
r.Locked = r.HasFormula
Next
End Sub
--
Gary's Student


"adodson" wrote:

I would like to lock formulas in cells on worksheet B if data is
entered
in
worksheet A. Otherwise, the user would need to enter data on top
of
the
formula.

How would I go about this?

I tried looking through other posts and couldn't find this... so
thanks
for
any assistance you may offer.

Lock Cell if another cell contains data
 

Thank you for your continued response.

I can see where it can be confusing, sorry. I tend to try to simplify
things because 1) I'm not sure on what information is needed since this area
is new to me (any beginning VB books you would recommend for working in
excel?), and 2) sometimes too much detail seems confusing when discussing an
overall graphic-visual-type problem.

I will try again tomorrow at work with the new information provided. Thanks.

"Tom Ogilvy" wrote:

Remember this:

Private Sub Worksheet_Change(ByVal Target As Range)
Call routine
End Sub


so:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not intersect(Target,Range("SpaceA")) is nothing then
'Range("spaceB").Parent.UnProtect
if application.Counta(range("SpaceA")) 0 then
Range("SpaceB").Locked = True
else
Range("SpaceB").Locked = False
End if
'Range("spaceB").Parent.Protect
end if
End Sub

You see, Space A and Space B mean nothing to me.

In my sample code, I assumed they were two named ranges. This code goes in
the sheet where you want the code to operate and where SpaceA and SpaceB
would be defined. Unfortunately, programming required knowledge and when
you don't explain thinks, people have to guess and make assumptions to
illustrate an approach.

If space A means a worksheet and space B means a worksheet then

This would go in the Sheet module of the sheet named SpaceA

Private Sub Worksheet_Change(ByVal Target As Range)
If Not intersect(Target,Worksheets("SpaceA").Range("A1:A1 0")) is nothing
then
Worksheets("spaceB").UnProtect
if application.Counta(worksheets("SpaceA").Range("A1: A10")) 0 then
Worksheets("SpaceB").Range("A1:A10").Locked = True
else
Worksheets("SpaceB").Range("A1:A10").Locked = False
End if
Worksheets("spaceB").Protect
end if
End Sub

--
Regards,
Tom Ogilvy


"adodson" wrote in message
...
Ok, I found how to refer to a worksheet/cell in the VB help files, but I
can't find anything about target or why it might be causing the error?

"adodson" wrote:

Forgive my inexperience, but when I put this code at the top of my edit
macro
area, and replace "space a" and "space b" with the names of the
worksheet, I
receive a compile error highlighting "Target" Any suggestions as to what
I'm
doing incorrect?

Also, how would I modify the code to check a specific cell or range of
cells
within "Space A" to block specific cells in "Space B"?

Thanks again for your assistance.

"Tom Ogilvy" wrote:

If Not intersect(Target,Range("SpaceA")) is nothing then
'Range("spaceB").Parent.UnProtect
if application.Counta(range("SpaceA")) 0 then
Range("SpaceB").Locked = True
else
Range("SpaceB").Locked = False
End if
'Range("spaceB").Parent.Protect
end if

--
Regards,
Tom Ogilvy



"adodson" wrote in message
...
If they enter stuff in space A, I want it to flow through to space B.
However, if nothing is entered in space A, I don't want space B to be
locked.
They have an option of whether they want to utilize space A or
provide
their
own version of space A separate.

Thank you for the responses.

"Tom Ogilvy" wrote:

This might be faster:

Sub routine()
Dim rng as Range
Dim w As Worksheet
Set w = Worksheets("sb")
'w.Unprotect
On Error Resume Next
set rng = w.UsedRange.SpecialCells(xlFormulas)
On Error goto 0
if not rng is nothing then
rng.Locked = True
End if
'w.Protect
End Sub

that said, I would say the OP needs to provide a better explanation
of
what
he wants, because his explanation doesn't make a whole lot of sense
to
me.
If the formulas are in worksheet B, why would entries in worksheet A
"need
to enter data on top of the formula." ?


--
Regards,
Tom Ogilvy


"Gary''s Student" wrote in
message
...
Say we have worksheets sa and sb. In worksheet code for sa enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Call routine
End Sub


In a standard module:

Sub routine()
Dim w As Worksheet
Set w = Worksheets("sb")
For Each r In w.UsedRange
r.Locked = r.HasFormula
Next
End Sub
--
Gary's Student


"adodson" wrote:

I would like to lock formulas in cells on worksheet B if data is
entered
in
worksheet A. Otherwise, the user would need to enter data on top
of
the
formula.

How would I go about this?

I tried looking through other posts and couldn't find this... so
thanks
for
any assistance you may offer.