+ Reply to Thread
Results 1 to 9 of 9

Make certain cells uneditable based on values in other cells

  1. #1
    Registered User
    Join Date
    05-04-2015
    Location
    South Africa
    MS-Off Ver
    office for Mac
    Posts
    97

    Make certain cells uneditable based on values in other cells

    Hi All,

    Hope someone can be of assistance. What macro must I use to make a certain cell uneditable based on the values of another cell. For instance, if cell "F60" is greater or equal to 51%, then cell "H60", must be editable, alternatively "H60" must be "No" and uneditable.

    I hope I make sense with my request, and any help would be greatly appreciated.

  2. #2
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: Make certain cells uneditable based on values in other cells

    You can use a Worksheet Selection Change event handler to monitor the cell:

    Please Login or Register  to view this content.

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Make certain cells uneditable based on values in other cells

    Hi, InvisibleMan,

    and what if F60 holds a formula? Your code would need to monitor the cells feeding the cell of interest or use Worksheet_Calculate instead, wouldn´t it?

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: Make certain cells uneditable based on values in other cells

    and what if F60 holds a formula?
    Not sure why that matters. The cell of interest, the cell to be locked, is cell H60. The event handler is monitoring selection, not change. When cell H60 is selected, it checks what is in cell F60 (rInterest.Offset(0, -2)). If the value in that cell is >= 51% it exits. Otherwise it fills it with "No"

    Never been a fan of Worksheet_Calculate event handlers as I'm sure they'll fire far too often for my liking.

  5. #5
    Registered User
    Join Date
    05-04-2015
    Location
    South Africa
    MS-Off Ver
    office for Mac
    Posts
    97

    Re: Make certain cells uneditable based on values in other cells

    Hi All,

    Thank you for your input and response. I am however a bit confused(btw I am not that experienced in VBA). The range that will depend whether the Cell "H60:H77" is blacked out or editable, is F60:F77. Therefore, if the values in these cells are equal or more than 51%, then the corresponding cell in the "H" range becomes uneditable/editable.

    How will this fit in the code above. Apologies for the questions, but I am a bit lost with this code.

  6. #6
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: Make certain cells uneditable based on values in other cells

    OK

    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: Make certain cells uneditable based on values in other cells

    Neater:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-04-2015
    Location
    South Africa
    MS-Off Ver
    office for Mac
    Posts
    97

    Re: Make certain cells uneditable based on values in other cells

    Hi,

    This works perfect, thank you very much. Is there no way one can "black" out the cell if it is less than 51%?

  9. #9
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: Make certain cells uneditable based on values in other cells

    Please Login or Register  to view this content.

+ 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. Replies: 3
    Last Post: 05-05-2014, 03:40 PM
  2. [SOLVED] If cell dropdown equals no, then make a range of cells uneditable?
    By jager in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-10-2013, 12:47 AM
  3. Can a macro make selections to slicers based on values or criteria in cells?
    By Yas420 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2013, 03:07 PM
  4. [SOLVED] Uneditable Range of Cells
    By krishnarao in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-28-2012, 05:40 AM
  5. [SOLVED] Making Cells with forumlas in them uneditable
    By bite_tony in forum Excel General
    Replies: 3
    Last Post: 04-25-2012, 06:49 AM
  6. uneditable cells and dynamic names of columns"
    By Samuurai in forum Excel General
    Replies: 7
    Last Post: 11-01-2009, 06:10 PM
  7. [SOLVED] Make cells uneditable, with general Cut/Copy functions intact
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-09-2006, 08:54 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