Posts by tkinnard

    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


    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


    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.

    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