将单元格数据验证设置为来自变量列表的列表

Am quite new to VBA and am piecing together my developments one step at at time searching for answers online but have hit a snag.

对于VBA来说，这是一个全新的概念，我正在把我的发展一步步拼凑在一起，在网上寻找答案，但遇到了障碍。

I'm struggling to understand how the creation of the data validation list code works. The beginning "With WrkBook.Range("H3").Validation" is where I'm putting the drop down list? Seems 'yes' from one posting, but another seemed to have the "With" followed by the actual range which contains the list. Secondly, the "Formula1:=..." I believe is also the location of the list. However, I seem to be only able to include the first cell in my list as being included in the list. Thanks in advance for any help.

我正在努力理解如何创建数据验证列表代码。一开始“WrkBook.Range(H3)。验证“我把下拉列表放在哪里?”从一篇文章中看起来“是”，但另一篇文章中似乎有“With”，后面跟着包含列表的实际范围。其次,“民用:=…”我相信这也是榜单的位置。然而，我似乎只能将列表中的第一个单元格包含在列表中。谢谢你的帮助。

Dim WrkBook As Worksheet
Dim LastCellRowNumber As Integer
Dim ListRng As Range
Dim Rng As Range

Set WrkBook = Worksheets("Misc Ref")

'Find
WrkBook.Activate
Range("A100000").Select
Range(Selection, Selection.End(xlUp)).Select

LastCellRowNumber = ActiveCell.Row

ActiveSheet.Cells(LastCellRowNumber, 1).Select

    Set ListRng = WrkBook.Range(Cells(2, 1), Cells(LastCellRowNumber, 1))

    With WrkBook.Range("H3").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=WrkBook.ListRng
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

1 个解决方案

#1

In this line:

在这条线:

 Set ListRng = WrkBook.Range(Cells(2, 1), Cells(LastCellRowNumber, 1))

you already set or assign a Range Object referenced at WrkBook sheet object.
So you don't have to use WrkBook.ListRng in assigning the Formula1 argument.
Also, Formula1 argument is suppose to be the address of the source range in the form of string.

您已经设置或分配了在WrkBook表对象中引用的范围对象。所以你不需要使用WrkBook。在分配公式参数时。同样，公式参数假设是字符串形式的源范围的地址。

So it should be something like:

所以应该是这样的:

Formula1:= "=" & ListRng.Address

This will fail though if the worksheet your putting the validation list is not the same worksheet of the source list. So you might want to add:

如果您放置验证列表的工作表与源列表的工作表不相同，则此操作将失败。你可能想补充一下

Formula1:= "=" & ListRng.Address(, , xlA1, True)

That will give you the Sheet name as well. HTH.

这也会给你表单的名称。HTH。

Edit2: Based on comments and no need to use Split function.

基于注释，不需要使用分割函数。

Sub test()
    Dim r As Range, lrow As Long
    With Sheets("Misc Ref")
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set r = .Range("A2:A" & lrow)
    End With
    'Debug.Print "=" & r.Address(, , xlA1, True)
    With Sheets("Summary").Range("H10").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & r.Address(, , xlA1, True)
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Setting the Address property External argument to True includes the full path.
But if you assign it to a validation formula, it automatically disregards the Workbook name.
So there is no need to use Split Function. No need to worry the apostrophe then. HTH.

将Address属性外部参数设置为True包含完整路径。但是如果您将它分配给验证公式，它会自动忽略工作簿的名称。所以没有必要使用分割函数。不用担心撇号。HTH。

#1