代码之家 › 专栏 › 技术社区 › coldpumpkin

Excel VBA从列表中要使用的两个不同区域创建名称(数据验证)

names list vba excel

-1

coldpumpkin · 技术社区 · 7 年前

是否可以从列表中使用的两个不同范围创建一个名称(数据验证)?

我有两个专栏: 包含从第1行到第3行的内容 C类

我试着用 ThisWorkbook.Names.Add Name:="TheList", RefersTo:=Range("A1:A3,C1:C4") 但是当插入 =TheList

是否有方法将两列中的值组合在一起?

1 回复 | 直到 7 年前

1

PeterT 7 年前

因为Excel需要一个连续的值范围来用作数据验证列表,所以您实际上有两个选项。

对于这个方法,我会在“Z”列(或者可能在另一个隐藏的工作表中)建立一个列表。下面是一个例子:

Option Explicit

Sub MakeValidationRange()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim dataRange As Range
    Set dataRange = ws.Range("A1:A3,C1:C3")

    Dim valRange As Range
    Set valRange = ws.Range("Z1")

    Dim entry As Variant
    For Each entry In dataRange
        valRange.Formula = "=" & entry.Address
        Set valRange = valRange.Offset(1, 0)
    Next entry
    Set valRange = ws.Range("Z1").CurrentRegion

    '--- alternate method using a named range
    ThisWorkbook.Names.Add Name:="TheList", RefersTo:=valRange

    Dim dropDownCell As Range
    Set dropDownCell = ws.Range("B1")
    dropDownCell.Validation.Delete
    dropDownCell.Validation.Add Type:=xlValidateList, _
                                AlertStyle:=xlValidAlertStop, _
                                Formula1:="=TheList"

    '--- alternate method without creating a named range
    Set dropDownCell = ws.Range("B2")
    dropDownCell.Validation.Delete
    dropDownCell.Validation.Add Type:=xlValidateList, _
                                AlertStyle:=xlValidAlertStop, _
                                Formula1:="=(" & valRange.Address & ")"

End Sub

其中的关键在于定义 .Validation.Add 方法等号。例如,验证列表将是 Red,Orange,Yellow,Green,Blue,Indigo,Violet . 没有双引号。没有空位。不等于。下面是一个例子:

Option Explicit

Sub MakeValidationList()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim dataRange As Range
    Set dataRange = ws.Range("A1:A3,C1:C3")

    Dim dataList As String
    Dim entry As Variant
    For Each entry In dataRange
        dataList = dataList & entry.Value & ","
    Next entry
    '--- remove the last trailing comma
    dataList = Left$(dataList, Len(dataList) - 1)

    Dim dropDownCell As Range
    Set dropDownCell = ws.Range("B3")
    dropDownCell.Validation.Delete
    dropDownCell.Validation.Add Type:=xlValidateList, _
                                AlertStyle:=xlValidAlertStop, _
                                Formula1:=dataList
End Sub