+ Reply to Thread
Results 1 to 8 of 8

Protect range of cells based on the value of other cells

  1. #1
    Registered User
    Join Date
    06-27-2010
    Location
    Cyprus
    MS-Off Ver
    Excel 2003
    Posts
    7

    Protect range of cells based on the value of other cells

    Hi

    I need to protect a range of cells based on the value of other cells.
    e.g. protect range ag13 to ag102 if cell ag12=0 and range ah13 to ah102 if cell ah12=0 and range ai13 to ai102 if cell ai12=0
    By the way my worksheet is protected.

    Any help?

    Thanks

  2. #2
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Karlstad, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    232

    Re: Protect range of cells based on the value of other cells

    Try this:

    ProtectCells.xlsm
    1. Reply to thread and inform if suggestion was helpful or not
    2. Click on the star (=Add Reputation) if you think someone helped you
    3. Mark [SOLVED] to this thread if solution was found. (On Menu "Thread Tools" > "Mark this thread as solved")

  3. #3
    Registered User
    Join Date
    06-27-2010
    Location
    Cyprus
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Protect range of cells based on the value of other cells

    Joakim,
    What is the use of button 1?

  4. #4
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Karlstad, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    232

    Re: Protect range of cells based on the value of other cells

    That's the button that runs the macro.

    Based on values in yellow cells, affects the Protection of range beneath. Value = 0 will Lock the range. All others result in unlocked cells

    Quote Originally Posted by airxl View Post
    Joakim,
    What is the use of button 1?

  5. #5
    Registered User
    Join Date
    06-27-2010
    Location
    Cyprus
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Protect range of cells based on the value of other cells

    But i don't want to press button 1 every time the values in the yellow cells change. I want the lock and unlock to be done automatically based on the values in the yellow cells.
    I just copied the code in my worksheet but it didn't work.
    What am I doing wrong.

  6. #6
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Karlstad, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    232

    Re: Protect range of cells based on the value of other cells

    Try this:

    ProtectCells.xlsm

  7. #7
    Registered User
    Join Date
    06-27-2010
    Location
    Cyprus
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Protect range of cells based on the value of other cells

    still not working in my worksheet. i don't know what i am doing wrong but anyway it seems that i have to show the protection password in the code something that i don't want to do since other people use this file.

    Thanks

  8. #8
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Karlstad, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    232

    Re: Protect range of cells based on the value of other cells

    If your worksheet is protected you must write the password in VBa to unprotect the sheet. But you can prevent users from viewing the VBA code:
    Tools, VBAProject Properties. Select tab Protection

+ 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. [SOLVED] Lock/Unlock range cells based on text value in other range cells
    By boboivan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-12-2014, 12:50 PM
  2. [SOLVED] Macro needed to clear cells in range based on value of cells in another range
    By msmith7113 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2013, 12:32 AM
  3. Entering in data in specific cells using range protect or locking the cells
    By howard101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2008, 11:05 AM
  4. Protect range of cells?
    By rockycj in forum Excel General
    Replies: 1
    Last Post: 02-07-2008, 03:36 PM
  5. protect cells based on another cell
    By Gary L Brown in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 06:05 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