+ Reply to Thread
Results 1 to 5 of 5

Data validation choice in one cell FORCES entry from data validation list in another

  1. #1
    Registered User
    Join Date
    06-17-2013
    Location
    Farnborough, Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    3

    Data validation choice in one cell FORCES entry from data validation list in another

    Hello.

    First of all - I have very little VBA training, but am trying to modify some existing code! I have programming experience so am not totally new.

    I'm looking for VBA code to help with the following:-

    In cell A1 I have a data validation list of options, say "Mouse,Cat,Horse,Human,Dog,Ape".
    In cell B1 I want to force a selection from another data validation list say "Anna,Bob,Chris,Doug,Erica" - but ONLY if the value in A1 is = "Human". If it's anything else I want nothing.
    I don't want anything in B1 UNLESS A1="Human".

    I believe I need some code in the Worksheet_Change Sub routine with a CASE statement which would pick up when A1 has changed.

    I feel it should be straightforward, but as I say, with only little knowledge I don't know where to start.

    The key thing is the FORCING of a selection in B1 if A1 is selected. I've seen various websites (Contextures etc) which talk about data validation on multiple columns, but nothing about making the selection mandatory.

    Thank you.

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Data validation choice in one cell FORCES entry from data validation list in another

    Try putting this in the worksheet's code:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-17-2013
    Location
    Farnborough, Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Data validation choice in one cell FORCES entry from data validation list in another

    Thank you.

    I can get this to populate B1 with the values, but it doesn't FORCE the user to select a value from the list. They can still set A1 = Human, and leave B1 empty.

    I imagine it needs some sort of loop in B1 which they can only leave if one of the values has been selected.

  4. #4
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Data validation choice in one cell FORCES entry from data validation list in another

    Try this. Since you want to forcefully choose one value, the best way I think is to just give a default value from the list

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-17-2013
    Location
    Farnborough, Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Data validation choice in one cell FORCES entry from data validation list in another

    Thanks. I'll give that a try

+ 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. Data Validation: Escaping Commas in Choice List
    By AdamBecker in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-24-2013, 10:55 AM
  2. Replies: 21
    Last Post: 03-14-2013, 07:33 AM
  3. Data validation forces entry in a range of cells.
    By markDuffy in forum Excel General
    Replies: 3
    Last Post: 02-10-2012, 08:46 AM
  4. Replies: 6
    Last Post: 07-12-2006, 09:14 PM
  5. Macro Forces a Check of Data Validation Before Saving or E-mailing
    By jbtenor1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-23-2006, 01:10 PM

Tags for this Thread

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