Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Lock cell based on contents of another (Excel 2002)

    Hi

    I would like to do the following

    If cell C24 contains "Standard" then Cell D24 would be locked
    If Cell C24 contains "Other" then Cell D24 would be unlocked

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lock cell based on contents of another (Excel 2002)

    And what if C24 contains something else than "Standard" or "Other"?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lock cell based on contents of another (Excel 2002)

    Hi Hans

    It can't because its set to data validation, it could be blank of course!!

    Sorry I took so long to reply

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lock cell based on contents of another (Excel 2002)

    So should D24 be locked or unlocked if the user clears C24?

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lock cell based on contents of another (Excel 2002)

    Maybe have something like this on a selection_change event or calculate event macro:
    <pre> If Range("C24") = "Other" Then
    Range("C24").Locked = False
    ActiveSheet.Unprotect
    Else
    Range("C24").Locked = True
    ActiveSheet.Protect '"Password"
    End If</pre>


    PS: This code is untested! Just a begin point!
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lock cell based on contents of another (Excel 2002)

    You'd use the Worksheet_Change event, not SelectionChange or Calculate.
    You'd also want to build in a test to see if C24 is involved, to avoid running the code whenever a cell is updated.
    I don't think you'd want to leave the sheet unprotected, instead, unprotect the sheet, lock/unlock D24, then protect the sheet again.
    For example:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C24")) Is Nothing Then
    Application.EnableEvents = False
    Me.Unprotect
    Range("D24").Locked = Not (Range("C24") = "Other")
    Me.Protect
    Application.EnableEvents = True
    End If
    End Sub

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock cell based on contents of another (Excel 2002)

    1- Using Selection change would be very inefficient. The code would be executed even if cell C24 had not changed. The worksheet change event would be a much better choice. The code should also check to see what cell was changed before checking and changing the locked property.

    2- Why are you unprotecting the sheet in one case and protecting it in the other? If you are locking or unlocking a cell, it seems to me you would want the sheet protected in all cases.

    3- The code does not take care of the case where cell C24 is something other than those two that were tested. Braddy60 has already told us that the cell could be blank but has not yet told us what should happen in that case.

    4- You cannot change the Locked property of a range if the sheet is protected. Therefore, the code should probably unprotect the sheet the sheet before setting the property and protect it after.
    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lock cell based on contents of another (Excel 2002)

    Hi Hans

    If C24 is cleared D24 should be locked.

    Thanks
    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lock cell based on contents of another (Excel 2002)

    Try the code in <post#=521,538>post 521,538</post: > higher up in this thread.

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lock cell based on contents of another (Excel 2002)

    Hi Legare

    The reason for locking an unlocking is because it relates to contract terms ie Standard relates to 30 days so if standard is selected you cannot enter other terms.

    However if Other is selected you will need to enter the new terms.

    Hope this is clear.

    Thanks
    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lock cell based on contents of another (Excel 2002)

    Hi Hans

    Thanks for the code I wil try it tomorrow,

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock cell based on contents of another (Excel 2002)

    My point was that for locked or unlocked to have an affect, the sheet must be protected. If the sheet is protected, then the code can not change the locked status. Therefore, the code must unprotect the sheet before it changes the locked property and then protect the sheet again after changing it.
    Legare Coleman

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lock cell based on contents of another (Excel 2002)

    Hi Hans

    I had cause to change the locked cell required from D24 to C26, However whilst the code does not produce an error, it does not lock the cell C26 when C24 is blank or contains Standard.

    Braddy

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C24")) Is Nothing Then
    Application.EnableEvents = False
    Me.Unprotect
    Range("C26").Locked = Not (Range("C24") = "Other")
    Me.Protect
    Application.EnableEvents = True
    End If
    End Sub
    If you are a fool at forty, you will always be a fool

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock cell based on contents of another (Excel 2002)

    Did you put the code in the module behind the worksheet. That code will not work in a normal module. Right click on the worksheet tab and in the popup menu select "View code." put the code there.
    Legare Coleman

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Lock cell based on contents of another (Excel 2002)

    Hi Legare

    I did put the code in the worksheet event please see the attached screenshot.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

Page 1 of 2 12 LastLast

Posting Permissions

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