驗證下拉條件 (Validation Drop down on a condition)

問題描述

驗證下拉條件 (Validation Drop down on a condition)

its a bit confusing to write my idea but ill give it a try. Here is what i was able to do. I have a drop down on my vertical cells, so i have a list of names to pick from which is linked by [name]‑> [define] and [data] ‑> [validation]. what i would like is another vertical cell to populate another drop down depending on the condition of the first.

for instance i have a list of fruits and vegetables. My first drop down menu asks the type so i select fruit. Because of such selection my second drop down will give me a list of fruits only (no vegetables) and vice versa.+

your help is greatly appreciated.

thanks gang!

if i want to name the cell instead of vegetable but 2009 and fruit 2010 i get an error. is there a way arround? thanks

‑‑‑‑‑

參考解法

方法 1:

Let's work with some named ranges.

Name E7 "option"

Put "vegetables" into E4 and "fruits" into E5;

in E7, Data > Validation > List, source $E$4:$E$5;

put some vegetables into H4:H7; name that range "vegetables";

put some fruits into J4:J7; name that range "fruits";

Now, in E8: Data > Validation > List, source "=INDIRECT(option)".

When you make a choice in E7, the options for E8 are set accordingly.

方法 2:

Here is my code for doing exactly what you are asking.

I have defined the independent column as a named range called Major_Category and set the drop down validation to a data list. I then have several other data lists that are named cat_subItems. So, for your example the major category would have items

• fruit
• vegetable

then I defined to more lists called

• cat_fruit
• cat_vegetable

which would contain the names of the fruits or vegetables.  Then based on the major category selection the Worksheet_change event will change the drop down validation in the next column over to either cat_fruit or cat_vegetable.

Note: This code does not play nice if you are using excel's protect worksheet. See this question for dealing with Excel's worksheet/book protection.

Public Sub Worksheet_Change(ByVal target As Range)
On Error GoTo ErrHandler:

Dim VRange As Range, cell As Range
Dim msg As String
Dim validateCode As Variant
Dim modCell As Range
Set VRange = Range("Major_Category")

If Intersect(VRange, target) Is Nothing Then Exit Sub

For Each cell In Intersect(VRange, target)
    b = cell.Value
    curRow = target.Row

    Set modCell = cell.Offset(0, 1) 'cell to modify the validation'

    If Not (b = "") Then
        modCell.Validation.Delete
        modCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        'sets the validation formula to the list/range name'
        xlBetween, Formula1:="=cat_" & b 

        modCell.Validation.IgnoreBlank = True
        modCell.Validation.InCellDropdown = True
        modCell.Validation.InputTitle = ""
        modCell.Validation.ErrorTitle = ""
        modCell.Validation.ErrorMessage = ""
        modCell.Validation.ShowInput = True
        modCell.Validation.ShowError = True

    End If
Next cell


Cleanup:

Exit Sub

ErrHandler:
MsgBox Err, vbOKOnly, "Error Occurred"
Resume Cleanup:

End Sub

方法 3:

You can't name a range "2009" ‑ because Excel would have trouble distinguishing that from the integer value 2009.  There are some other restrictions, too ‑ you couldn't name a range "A1" because Excel would get confused with the cell A1.  What you could do instead is have the 2009 and 2010 in a table, and beside them put the names of the ranges, then do a VLOOKUP to get the range name.  Alternately, you could use, say, "_2009" and have your formula as 

=INDIRECT("_" & option)

(by Oleg Tarassov、Carl Manaster、Azim J、Carl Manaster)

參考文件

1. Validation Drop down on a condition (CC BY‑SA 3.0/4.0)