+ Reply to Thread
Results 1 to 3 of 3

Conditional Locking of Cells- More than one Sub needed?

  1. #1
    Registered User
    Join Date
    05-06-2009
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    85

    Conditional Locking of Cells- More than one Sub needed?

    I am trying to accomplish what was described at the link I provided below, but I have one additional problem.

    I am trying to create a form that has multiple drop downs, which are dependent on answers from other drop-downs. I'm trying to make it super user friendly and have the cells lock, so that the user can tab through only the items that they need. I have it almost down, but I'm not sure how to do it for multiple cells. The closest I come is one big long if,else statement which runs through the whole thing on every cell change.



    I have an example of the spreadsheet attached.
    If F1 says "Other", I want H1 to be unlocked
    If F2 says "Y", I want F3 and F4 to be unlocked
    IF F4 says "Other", I want H4 to be unlocked

    This is a very small portion of the form, but I'm sure that it is enough to point me in the right direction if someone can help!!

    Many thanks!!
    Attached Files Attached Files
    Last edited by kellyfspringer; 08-19-2009 at 09:22 PM.

  2. #2
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: Conditional Locking of Cells- More than one Sub needed?

    Use the following code, by right clicking on the sheet tab, then 'View Code' and paste this code into the VBA editor.
    Please Login or Register  to view this content.
    I have also re-attached your sample workbook with the code loaded in it.

    NOTE I have Un-merged cells H1 and H4, and instead formatted them as 'center-across-selection'.
    Merged cells are the bain of VBA coders as they cause problems with many of VBA's functions, (least of all you can't lock/unlock merged cells). It is generally better to leave the cell un-merged.
    If you have other cells on your actual sheet that you need to do this to then follow these steps.

    1) Select a merged cell.
    2) Unmerge the cell.
    3) Select the cells that previous made up the 'merged cell'
    4) Select 'Format' --> 'Cells'
    5) From the Alignment tab, under horizontal select 'Center across selection'.

    Doing this means that the data will only be in cell H1, (or H4), as far as VBA is concerned, but to the user it will still look as though the cell is merged

    If you have any questions please do ask.
    Attached Files Attached Files
    If you find the response helpful please click the scales in the blue bar above and rate it
    If you don't like the response, don't bother with the scales, they are not for you

  3. #3
    Registered User
    Join Date
    05-06-2009
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    85

    Re: Conditional Locking of Cells- More than one Sub needed?

    Oh, Thank you so very much! The way I was writing it, it was very long (was making me nervous) and repetitive (making me think there must be a better way), not to mention that I could see excel doing the steps really quickly. With what you did, it even feels more solid.

    Your taking me from A1+A1+A3+A4+A5 to sum(A1:A5). Many thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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