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.

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.

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.

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.