lock cells based on negative value in another cell

  • I am looking for a VBA / Macro to lock a range of cells ( D101-D192 ) if the calculated value in R1 falls bellow $1.00.


    This is what I have so far and it is not working:


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Range("R1") < "1" Then
    3. ActiveSheet.unprotect
    4. Range("D101:D192").Locked = True
    5. ActiveSheet.Protect
    6. Else
    7. End If
    8. End Sub
  • Re: lock cells based on negative value in another cell


    If R1 is changing as the result of a formula then it will not trigger the worksheet change event.


    Maybe the worksheet calculate event would be a better choice.


    Also if R1 contains a number you would not use quotes around it, <"1" should just be <1.

  • Re: lock cells based on negative value in another cell


    The workbook that I have is a simple order form that sums up column E which is referenced in R2 as [ =$R$1-$F$193 ], R1 on the next page then references R2 as [ =SHEETOFFSET(-1,R2) ].


    My desire is when the allowance gets down to zero that it locks the column that is used to place the next order. Then they will need to make contact with me for approval for going over.


    I'm a firemen that works in the office as a purchasing agent and take orders from the 11 stations for their station needs. I have a little experience with pc's but not enough to get through the codes.

  • Re: lock cells based on negative value in another cell


    Right click on the sheet tab for the sheet that has R1.


    Choose view code and paste this code into the white window.


    Make some changes that will cause R1 to be below and greater than the threshold to test if the code works.


  • Re: lock cells based on negative value in another cell


    Ok, so I placed the code in there and it is almost working correctly. Ass soon as I entered an item that would make the budgeted allowance drop below zero it automatically went to the next sheet and asked for a password. I really would not want it to do this at this exact moment. Also if you selct cancel it gives a Run-time error '1004' Unable to set the locked property of the Range class. This esintially negates the desired effect I am looking for.


    I also closed the books then re-opened them, instead of automatically going to my "Splash Page" basically a title page showing them their balances it took me to the page that was locked asking for the password again. Once again I selected cancel and received the same error.


    Very close though.

  • Re: lock cells based on negative value in another cell


    Try this. :cool:


    Code
    1. Private Sub Worksheet_Calculate()
    2. Me.Unprotect Password:="put your password here"
    3. If Range("R1") < 1 Then
    4. Range("D101:D192").Locked = True
    5. Me.Protect
    6. Else
    7. Range("D101:D192").Locked = False
    8. End If
    9. Me.Protect Password:="put your password here"
    10. End Sub
  • Re: lock cells based on negative value in another cell


    Oops, I might have spoken too soon. After placing the code it worked so well that I placed in up to 10 other sheets and now it is giving me an error. This time it is a Run-time error 1004 The password you supplied is not correct. At no time did I try to enter a password.


    Can I just place this a a module for the whole book?

  • Re: lock cells based on negative value in another cell


    Thanks for the link and because my brother is a retired Fire Fighter\Arson Investigator and the Local Paramedics helped my Mother, more than once, let me help you understand.


    Try not to crosspost, have some patience.
    If you insist on crossposting make sure you fill in those links.
    There are two things that may happen, maybe more.
    1) When people figure out you've crossposted they'll just ignore your post completely.
    2) Everyone on these boards have an ignore list to which they may add your name which means if you post in the future they don't have to remember that you are someone they don't wish to help they will be reminded of it.


    Most regulars on these boards are regulars on other boards so as you have seen you most likely will be busted if you crosspost without links.


    Quote from tkinnard;777119

    Once I started getting assistance I tried to delete the other one. But I have linked it to this one. Thanks for your assistance.


    http://www.excelforum.com/exce…alue-in-another-cell.html

  • Re: lock cells based on negative value in another cell


    Just to rationalise it a little.


    Quote

    I placed in up to 10 other sheets


    You don't have to add the same code to multiple sheets. You can use the WorkBook_SheetCalculate event in the ThisWorkbook class to have 1 'common' routine to do this. The only complications are you need to think of the best way to decide which sheets the code relates to and every sheet must have the same password or you need to add code to assign it based on the sheet name.



    That includes just the essential changes needed to move this to the workbook module. The code itself can be further optimised like so (No comments - all explained above):