say i have 2 cells B20 and B21. when i enter a number in B20 i want B21 to
lock, i also want this to work via versa so if i enter a number in B21 i want
B20 to lock. this is so a number cannot be put in both cells
is this possible
thankyou john tempest

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Range("B20").Value < "" Then
Range("B21").Locked = True
Else
Range("B21").Locked = False
End If
If Range("B21").Value < "" Then
Range("B20").Locked = True
Else
Range("B20").Locked = False
End If
ActiveSheet.Protect
End Sub

--
Ian
--
"john tempest" wrote in message
...
say i have 2 cells B20 and B21. when i enter a number in B20 i want B21 to
lock, i also want this to work via versa so if i enter a number in B21 i
want
B20 to lock. this is so a number cannot be put in both cells
is this possible
thankyou john tempest

thank you ian but i am still having problems. i get the error ambiguous which
i can understand as i have the following code already in the sheet. how do i
get both sets of code to work on the same sheet
thans again john
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range

Set WatchRange = Range("B4:B7,B25,B16:H16")

If Application.CountA(WatchRange) = WatchRange.Count Then

Sheet111.Visible = xlSheetVisible
Else
Sheet111.Visible = xlSheetVeryHidden
End If
End Sub

"Ian" wrote:

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Range("B20").Value < "" Then
Range("B21").Locked = True
Else
Range("B21").Locked = False
End If
If Range("B21").Value < "" Then
Range("B20").Locked = True
Else
Range("B20").Locked = False
End If
ActiveSheet.Protect
End Sub

--
Ian
--
"john tempest" wrote in message
...
say i have 2 cells B20 and B21. when i enter a number in B20 i want B21 to
lock, i also want this to work via versa so if i enter a number in B21 i
want
B20 to lock. this is so a number cannot be put in both cells
is this possible
thankyou john tempest

I think the problem is that you have 2 subs with the same name. If you put
all the code under the same sub, you should be OK.

If you paste my code immediately after yours, then delete the End Sub at the
end of your code and the Private Sub line at the beginning of mine, it
should work. As my code protects the sheet after runnign, you may find you
have to move the Activesheet.Unprotect line to the top of the code to avoid
any problems.
--
Ian
--
"john tempest" wrote in message
...
thank you ian but i am still having problems. i get the error ambiguous
which
i can understand as i have the following code already in the sheet. how do
i
get both sets of code to work on the same sheet
thans again john
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range

Set WatchRange = Range("B4:B7,B25,B16:H16")

If Application.CountA(WatchRange) = WatchRange.Count Then

Sheet111.Visible = xlSheetVisible
Else
Sheet111.Visible = xlSheetVeryHidden
End If
End Sub

"Ian" wrote:

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Range("B20").Value < "" Then
Range("B21").Locked = True
Else
Range("B21").Locked = False
End If
If Range("B21").Value < "" Then
Range("B20").Locked = True
Else
Range("B20").Locked = False
End If
ActiveSheet.Protect
End Sub

--
Ian
--
"john tempest" wrote in message
...
say i have 2 cells B20 and B21. when i enter a number in B20 i want B21
to
lock, i also want this to work via versa so if i enter a number in B21
i
want
B20 to lock. this is so a number cannot be put in both cells
is this possible
thankyou john tempest

thank you ian that now works great
john

"Ian" wrote:

I think the problem is that you have 2 subs with the same name. If you put
all the code under the same sub, you should be OK.

If you paste my code immediately after yours, then delete the End Sub at the
end of your code and the Private Sub line at the beginning of mine, it
should work. As my code protects the sheet after runnign, you may find you
have to move the Activesheet.Unprotect line to the top of the code to avoid
any problems.
--
Ian
--
"john tempest" wrote in message
...
thank you ian but i am still having problems. i get the error ambiguous
which
i can understand as i have the following code already in the sheet. how do
i
get both sets of code to work on the same sheet
thans again john
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range

Set WatchRange = Range("B4:B7,B25,B16:H16")

If Application.CountA(WatchRange) = WatchRange.Count Then

Sheet111.Visible = xlSheetVisible
Else
Sheet111.Visible = xlSheetVeryHidden
End If
End Sub

"Ian" wrote:

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Range("B20").Value < "" Then
Range("B21").Locked = True
Else
Range("B21").Locked = False
End If
If Range("B21").Value < "" Then
Range("B20").Locked = True
Else
Range("B20").Locked = False
End If
ActiveSheet.Protect
End Sub

--
Ian
--
"john tempest" wrote in message
...
say i have 2 cells B20 and B21. when i enter a number in B20 i want B21
to
lock, i also want this to work via versa so if i enter a number in B21
i
want
B20 to lock. this is so a number cannot be put in both cells
is this possible
thankyou john tempest

Thanks for the feedback.

--
Ian
--
"john tempest" wrote in message
...
thank you ian that now works great
john

"Ian" wrote:

I think the problem is that you have 2 subs with the same name. If you
put
all the code under the same sub, you should be OK.

If you paste my code immediately after yours, then delete the End Sub at
the
end of your code and the Private Sub line at the beginning of mine, it
should work. As my code protects the sheet after runnign, you may find
you
have to move the Activesheet.Unprotect line to the top of the code to
avoid
any problems.
--
Ian
--
"john tempest" wrote in message
...
thank you ian but i am still having problems. i get the error ambiguous
which
i can understand as i have the following code already in the sheet. how
do
i
get both sets of code to work on the same sheet
thans again john
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range

Set WatchRange = Range("B4:B7,B25,B16:H16")

If Application.CountA(WatchRange) = WatchRange.Count Then

Sheet111.Visible = xlSheetVisible
Else
Sheet111.Visible = xlSheetVeryHidden
End If
End Sub

"Ian" wrote:

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Range("B20").Value < "" Then
Range("B21").Locked = True
Else
Range("B21").Locked = False
End If
If Range("B21").Value < "" Then
Range("B20").Locked = True
Else
Range("B20").Locked = False
End If
ActiveSheet.Protect
End Sub

--
Ian
--
"john tempest" wrote in
message
...
say i have 2 cells B20 and B21. when i enter a number in B20 i want
B21
to
lock, i also want this to work via versa so if i enter a number in
B21
i
want
B20 to lock. this is so a number cannot be put in both cells
is this possible
thankyou john tempest

ian
i have come across an alternative that might interest you

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Trim(Range("B20").Value) < "" And ActiveCell.Address = "$B$21" Then
Range("B20").Select
If Trim(Range("B21").Value) < "" And ActiveCell.Address = "$B$20" Then
Range("B21").Select
End Sub
regards john
"Ian" wrote:

Thanks for the feedback.

--
Ian
--
"john tempest" wrote in message
...
thank you ian that now works great
john

"Ian" wrote:

I think the problem is that you have 2 subs with the same name. If you
put
all the code under the same sub, you should be OK.

If you paste my code immediately after yours, then delete the End Sub at
the
end of your code and the Private Sub line at the beginning of mine, it
should work. As my code protects the sheet after runnign, you may find
you
have to move the Activesheet.Unprotect line to the top of the code to
avoid
any problems.
--
Ian
--
"john tempest" wrote in message
...
thank you ian but i am still having problems. i get the error ambiguous
which
i can understand as i have the following code already in the sheet. how
do
i
get both sets of code to work on the same sheet
thans again john
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range

Set WatchRange = Range("B4:B7,B25,B16:H16")

If Application.CountA(WatchRange) = WatchRange.Count Then

Sheet111.Visible = xlSheetVisible
Else
Sheet111.Visible = xlSheetVeryHidden
End If
End Sub

"Ian" wrote:

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Range("B20").Value < "" Then
Range("B21").Locked = True
Else
Range("B21").Locked = False
End If
If Range("B21").Value < "" Then
Range("B20").Locked = True
Else
Range("B20").Locked = False
End If
ActiveSheet.Protect
End Sub

--
Ian
--
"john tempest" wrote in
message
...
say i have 2 cells B20 and B21. when i enter a number in B20 i want
B21
to
lock, i also want this to work via versa so if i enter a number in
B21
i
want
B20 to lock. this is so a number cannot be put in both cells
is this possible
thankyou john tempest

As ever, there are different ways to do things. This simply redirects the
active cell to the one with data in it, thereby making it impossible to
enter data in the second cell. It also does away with the need to lock the
cells and the sheet. Unless you need to lock the sheeet for other reasons,
it appears to be a more elegant solution.

--
Ian
--
"john tempest" wrote in message
...
ian
i have come across an alternative that might interest you

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Trim(Range("B20").Value) < "" And ActiveCell.Address = "$B$21" Then
Range("B20").Select
If Trim(Range("B21").Value) < "" And ActiveCell.Address = "$B$20" Then
Range("B21").Select
End Sub
regards john
"Ian" wrote:

Thanks for the feedback.

--
Ian
--
"john tempest" wrote in message
...
thank you ian that now works great
john

"Ian" wrote:

I think the problem is that you have 2 subs with the same name. If you
put
all the code under the same sub, you should be OK.

If you paste my code immediately after yours, then delete the End Sub
at
the
end of your code and the Private Sub line at the beginning of mine, it
should work. As my code protects the sheet after runnign, you may find
you
have to move the Activesheet.Unprotect line to the top of the code to
avoid
any problems.
--
Ian
--
"john tempest" wrote in
message
...
thank you ian but i am still having problems. i get the error
ambiguous
which
i can understand as i have the following code already in the sheet.
how
do
i
get both sets of code to work on the same sheet
thans again john
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range

Set WatchRange = Range("B4:B7,B25,B16:H16")

If Application.CountA(WatchRange) = WatchRange.Count Then

Sheet111.Visible = xlSheetVisible
Else
Sheet111.Visible = xlSheetVeryHidden
End If
End Sub

"Ian" wrote:

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Range("B20").Value < "" Then
Range("B21").Locked = True
Else
Range("B21").Locked = False
End If
If Range("B21").Value < "" Then
Range("B20").Locked = True
Else
Range("B20").Locked = False
End If
ActiveSheet.Protect
End Sub

--
Ian
--
"john tempest" wrote in
message
...
say i have 2 cells B20 and B21. when i enter a number in B20 i
want
B21
to
lock, i also want this to work via versa so if i enter a number
in
B21
i
want
B20 to lock. this is so a number cannot be put in both cells
is this possible
thankyou john tempest