+ Reply to Thread
Results 1 to 3 of 3

lock cells based on negative value in another cell

  1. #1
    Registered User
    Join Date
    09-09-2016
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    2

    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:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("R1") < 1 Then
    ActiveSheet.unprotect
    Range("D101:D192").Locked = True
    ActiveSheet.Protect
    Else
    End If
    End Sub


    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.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,708

    Re: lock cells based on negative value in another cell

    Check out worksheet_calculate instead of worksheet_change events

  3. #3
    Registered User
    Join Date
    09-09-2016
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    2

    Re: lock cells based on negative value in another cell

    Got a start on it and almost there.

    http://www.ozgrid.com/forum/showthread.php?t=201088

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Lock Cells based on value of another cell in a Table
    By 2dang in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-14-2016, 09:51 AM
  2. Lock/Unlock cells based on value in another cell
    By 13gentj in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-15-2014, 11:00 PM
  3. Lock/Protect Cells based on the color of cell
    By Nisha_NishaDelhi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-27-2014, 07:26 AM
  4. [SOLVED] VBA to lock a row based on the value of a cell (worksheet has some locked cells)
    By MissDB in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2014, 10:59 AM
  5. lock cells based on another cell value inexcel
    By chavanalini in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-12-2014, 01:53 PM
  6. lock/unlock cell based on another cells input
    By 2funny4words in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-21-2013, 03:17 PM
  7. Lock cells within a row based on cell value
    By PY_ in forum Excel General
    Replies: 2
    Last Post: 11-14-2010, 12:40 AM

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