+ Reply to Thread
Results 1 to 3 of 3

Dynamic Data Validation

  1. #1
    Alex
    Guest

    Dynamic Data Validation

    Hello

    What I want to be able to do is to have two dropdown boxes on a worksheet so
    that the selection in the first (dropdown1) governs what is shown in the
    second (dropdown2).

    In cell A1 I have used data validation to display the range A2:A4. This
    displays "Head Office", "Branch1", & "Branch2". These are my offices. When I
    select "Head Office" I want dropdown2 (in cell B1) to display range B2:B4
    which has three names of employees employed at Head Office. Similarly, if
    "Branch1" is selected then dropdown2 will disply C2:C4 which contains the
    names of my Branch1 employees. Simple enough?

    I have tried Data Validation and then selecting 'Custom' and typing in a
    formula along the lines of...

    IF(A1="Head Office",B2:B4,"")

    but this does not work.

    I tried using VBA events handlers. I tried using Worksheet_Change(ByVal as
    Excel.Range) but when I change the selection in dropdown1 there is no effect.
    It seems like VBA is not recognising the event. (By the way, I have used
    EventEnabler = False etc).

    It seems to me that my problem must have been encountered before. Any ideas?

    I would like to avoid using VBA list boxes of comboboxes as they look messy
    on the sheet and cannot be sized to a single excel cell.

    I hope there is help at hand...

    Alex

  2. #2
    keepITcool
    Guest

    Re: Dynamic Data Validation

    bookmark Debra's site
    http://www.contextures.com/tiptech.html

    look for datavalidation - dependent lists



    --
    keepITcool
    | www.XLsupport.com | keepITcool chello nl | amsterdam


    Alex wrote :

    > Hello
    >
    > What I want to be able to do is to have two dropdown boxes on a
    > worksheet so that the selection in the first (dropdown1) governs what
    > is shown in the second (dropdown2).
    >
    > In cell A1 I have used data validation to display the range A2:A4.
    > This displays "Head Office", "Branch1", & "Branch2". These are my
    > offices. When I select "Head Office" I want dropdown2 (in cell B1) to
    > display range B2:B4 which has three names of employees employed at
    > Head Office. Similarly, if "Branch1" is selected then dropdown2 will
    > disply C2:C4 which contains the names of my Branch1 employees. Simple
    > enough?
    >
    > I have tried Data Validation and then selecting 'Custom' and typing
    > in a formula along the lines of...
    >
    > IF(A1="Head Office",B2:B4,"")
    >
    > but this does not work.
    >
    > I tried using VBA events handlers. I tried using
    > Worksheet_Change(ByVal as Excel.Range) but when I change the
    > selection in dropdown1 there is no effect. It seems like VBA is not
    > recognising the event. (By the way, I have used EventEnabler = False
    > etc).
    >
    > It seems to me that my problem must have been encountered before. Any
    > ideas?
    >
    > I would like to avoid using VBA list boxes of comboboxes as they look
    > messy on the sheet and cannot be sized to a single excel cell.
    >
    > I hope there is help at hand...
    >
    > Alex


  3. #3
    Bob Phillips
    Guest

    Re: Dynamic Data Validation

    See http://www.xldynamic.com/source/xld.Dropdowns.html

    --
    HTH

    Bob Phillips

    "Alex" <[email protected]> wrote in message
    news:[email protected]...
    > Hello
    >
    > What I want to be able to do is to have two dropdown boxes on a worksheet

    so
    > that the selection in the first (dropdown1) governs what is shown in the
    > second (dropdown2).
    >
    > In cell A1 I have used data validation to display the range A2:A4. This
    > displays "Head Office", "Branch1", & "Branch2". These are my offices. When

    I
    > select "Head Office" I want dropdown2 (in cell B1) to display range B2:B4
    > which has three names of employees employed at Head Office. Similarly, if
    > "Branch1" is selected then dropdown2 will disply C2:C4 which contains the
    > names of my Branch1 employees. Simple enough?
    >
    > I have tried Data Validation and then selecting 'Custom' and typing in a
    > formula along the lines of...
    >
    > IF(A1="Head Office",B2:B4,"")
    >
    > but this does not work.
    >
    > I tried using VBA events handlers. I tried using Worksheet_Change(ByVal as
    > Excel.Range) but when I change the selection in dropdown1 there is no

    effect.
    > It seems like VBA is not recognising the event. (By the way, I have used
    > EventEnabler = False etc).
    >
    > It seems to me that my problem must have been encountered before. Any

    ideas?
    >
    > I would like to avoid using VBA list boxes of comboboxes as they look

    messy
    > on the sheet and cannot be sized to a single excel cell.
    >
    > I hope there is help at hand...
    >
    > Alex




+ 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