+ Reply to Thread
Results 1 to 9 of 9

lock/unlock cells

  1. #1
    john tempest
    Guest

    lock/unlock cells

    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

  2. #2
    Ian
    Guest

    Re: lock/unlock cells

    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" <johntempest@discussions.microsoft.com> wrote in message
    news:B39477F2-09AA-49E1-B022-232802A9E720@microsoft.com...
    > 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




  3. #3
    john tempest
    Guest

    Re: lock/unlock cells

    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" <johntempest@discussions.microsoft.com> wrote in message
    > news:B39477F2-09AA-49E1-B022-232802A9E720@microsoft.com...
    > > 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

    >
    >
    >


  4. #4
    Ian
    Guest

    Re: lock/unlock cells

    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" <johntempest@discussions.microsoft.com> wrote in message
    news:462423F8-6EAA-40D8-BDDB-73E49359BCD8@microsoft.com...
    > 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" <johntempest@discussions.microsoft.com> wrote in message
    >> news:B39477F2-09AA-49E1-B022-232802A9E720@microsoft.com...
    >> > 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

    >>
    >>
    >>




  5. #5
    john tempest
    Guest

    Re: lock/unlock cells

    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" <johntempest@discussions.microsoft.com> wrote in message
    > news:462423F8-6EAA-40D8-BDDB-73E49359BCD8@microsoft.com...
    > > 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" <johntempest@discussions.microsoft.com> wrote in message
    > >> news:B39477F2-09AA-49E1-B022-232802A9E720@microsoft.com...
    > >> > 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
    > >>
    > >>
    > >>

    >
    >
    >


  6. #6
    Ian
    Guest

    Re: lock/unlock cells

    Thanks for the feedback.

    --
    Ian
    --
    "john tempest" <johntempest@discussions.microsoft.com> wrote in message
    news:954D4C31-A22E-44B9-8CC2-C488D002CE86@microsoft.com...
    > 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" <johntempest@discussions.microsoft.com> wrote in message
    >> news:462423F8-6EAA-40D8-BDDB-73E49359BCD8@microsoft.com...
    >> > 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" <johntempest@discussions.microsoft.com> wrote in
    >> >> message
    >> >> news:B39477F2-09AA-49E1-B022-232802A9E720@microsoft.com...
    >> >> > 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
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  7. #7
    john tempest
    Guest

    Re: lock/unlock cells

    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" <johntempest@discussions.microsoft.com> wrote in message
    > news:954D4C31-A22E-44B9-8CC2-C488D002CE86@microsoft.com...
    > > 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" <johntempest@discussions.microsoft.com> wrote in message
    > >> news:462423F8-6EAA-40D8-BDDB-73E49359BCD8@microsoft.com...
    > >> > 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" <johntempest@discussions.microsoft.com> wrote in
    > >> >> message
    > >> >> news:B39477F2-09AA-49E1-B022-232802A9E720@microsoft.com...
    > >> >> > 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
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


  8. #8
    john tempest
    Guest

    Re: lock/unlock cells

    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" <johntempest@discussions.microsoft.com> wrote in message
    > news:954D4C31-A22E-44B9-8CC2-C488D002CE86@microsoft.com...
    > > 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" <johntempest@discussions.microsoft.com> wrote in message
    > >> news:462423F8-6EAA-40D8-BDDB-73E49359BCD8@microsoft.com...
    > >> > 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" <johntempest@discussions.microsoft.com> wrote in
    > >> >> message
    > >> >> news:B39477F2-09AA-49E1-B022-232802A9E720@microsoft.com...
    > >> >> > 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
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


  9. #9
    Ian
    Guest

    Re: lock/unlock cells

    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" <johntempest@discussions.microsoft.com> wrote in message
    news:C382BE83-93EB-4944-9401-2ED2BEA36C63@microsoft.com...
    > 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" <johntempest@discussions.microsoft.com> wrote in message
    >> news:954D4C31-A22E-44B9-8CC2-C488D002CE86@microsoft.com...
    >> > 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" <johntempest@discussions.microsoft.com> wrote in
    >> >> message
    >> >> news:462423F8-6EAA-40D8-BDDB-73E49359BCD8@microsoft.com...
    >> >> > 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" <johntempest@discussions.microsoft.com> wrote in
    >> >> >> message
    >> >> >> news:B39477F2-09AA-49E1-B022-232802A9E720@microsoft.com...
    >> >> >> > 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
    >> >> >>
    >> >> >>
    >> >> >>
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1