+ Reply to Thread
Results 1 to 10 of 10

Conditional locking of cell based on another cell

  1. #1
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184

    Conditional locking of cell based on another cell

    I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password. Thanks!

    Please Login or Register  to view this content.
    Last edited by jman0707; 10-11-2008 at 08:33 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    the code you have is a bit hard to follow 7 it is not necessay to slect any cells. I think this is what you want
    Please Login or Register  to view this content.
    Last edited by royUK; 10-10-2008 at 02:53 PM.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    The password function works. But here's what I need. If there is a positive number (or any number, I've validated it for only positive) in H19, H20 locks. And once I reset H19 to 0, H20 opens. And vice versa. If I put a value in H20, H19 locks until I put 0 in H20 again. And with this code, as soon as I put a value in the first cell (either H19 or H20) the other locks, but DOES NOT re-open when I put the value back to 0 in the open cell. Hope that makes sense. Thanks!

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    The code works on the basis that there is a value in the cell - positive or negative. If you delete the entry then the cell is unlocked. Maybe this is what you are looking for
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    No, it's still not working. You are right, I need to just be able to put 0 and have it unlock, not delete altogether. But what's happening is let's say you put a value in H19. H20 does lock, but when you then put 0 in H19, H19 locks. In that case they should both be open, because they both say 0. And the same happens in reverse. I think that's what all that code was for, but I no nothing about programming! Also, with the other code, it jumped back up to H19 when i typed 0 in H20, and jumped to H20 when I typed 0 in H19 so they were both ready for cell entry. That's pretty much convenience though! Would it be easier to just add the password string or whatever to the other code? If not, is there a way to fix that quirk so that anytime either cell is 0 it's unlocked? Thanks a ton RoyUK!

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    When either cell contains a value <> 0 then the other cell is Locked. If either contain 0 both are unlocked

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    Yep, works perfectly! Thanks for your help Roy!

  8. #8
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    So for learning purposes, what does the option explicit do?

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Option Explicit forces you to declare all your variables, see
    http://www.excel-it.com/clear_code.htm

  10. #10
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    ok thanks!

+ 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. conditional cell based on actions of another cell
    By florischu in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-03-2008, 07:29 PM
  2. Conditional Formating with reference to another cell
    By Spencinator in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2008, 05:56 AM
  3. Conditional Formatting a cell which is showing a %
    By dandavis1 in forum Excel General
    Replies: 2
    Last Post: 09-08-2008, 08:03 AM
  4. Conditional Format- Blank cell with formula problem
    By Trappy in forum Excel General
    Replies: 4
    Last Post: 08-20-2007, 09:51 AM
  5. Conditional Formatting based on 3 different cell values (criteria)
    By xlguy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-05-2007, 05:36 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