Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    This is my first post but I'm amazed at the speed of response others have got so I thought I'd give it a try!

    To spell it out as plainly as I can:

    To start I want cells F6:H16 to be locked.

    Then when either "existing" or "install" is input into E6:E16, I want F6:F16 to unlock

    Then when either "existing" or "install" is input into F6:F16, I want G6:G16 to unlock

    Then when either "existing" or "install" is input into G6:G16, I want H6:H16 to unlock

    It's fair to say I'm a beginner macro user so please don't assume much in explaining what I should do!

    Thanks very much in advance
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Welcome to the Lounge Matt!

    I imagine that this *is* possible, but this is a little over my head. I'll have a play around and see if I can come up with anything (no promises)

    Hopefully, one of our resident guru's will be along soon.

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    You could do this by using a Worksheet_Change event and macro code that looks at which cell(s) have changed and locks / unlocks the corresponding cells to the right (which is what the code in your worksheet is trying to do).

    This method has a number of disadvantages, the worst of which is that it completely disables Excel's undo feature.

    An alternative approach is to change the source of the data validation list for each cell, depending on what has been entered in the cell to its left. This wouldn't "lock" the cell but it would prevent any value from being entered other than those you expect.

    If you change the Data Validation list for cell F6 from
    =$R$5:$R$7
    to
    =IF(ISNA(MATCH(E6,$R$5:$R$6,)),$R$7,$R$5:$R$7)
    Clear the validation Ignore Blank checkbox
    Then copy this cell and Paste Special > Validation to the rest of cells F6:H16 I think it will do what you want. Have a look at the attached spreadsheet and see if it works for you.

    [attachment=85439:Calculat..._08_09MG.xls]
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's neat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •