VBA中的Excel验证下拉长度问题 [英] Excel Validation Drop Down length issue in VBA

问题描述

我是VBA的新手.我在一个旧项目中有一个VBA excel宏编码(使用VBA的Excel验证下拉列表).何时下拉到最大数量的列表我得到了错误.下拉数据是从另一张纸上收集的

I am new to VBA. I am having a VBA excel macro coding(Excel Validation Drop Down list using VBA) in a old project. When the drop-down going to a maximum number of list I am getting the error.The drop down data are collected from another sheet

下面是屏幕截图

Public Sub CORE_SetValidation(ByRef Rng As Range, ByVal Value As String)
    With Rng.Validation
        Call .Delete
        If Value <> "" Then
            Call .Add(Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Value)
            .ErrorMessage = "Please select a value from drop-down list"
            .ErrorTitle = "Value Error"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputMessage = ""
            .InputTitle = ""
            .ShowInput = True
            .ShowError = True
        End If
    End With
End Sub

1. VBA下拉列表中的字符或下拉列表是否有任何限制，因为从错误消息中我无法预测问题.
2. 我能否获得确切的错误消息.

推荐答案

不要使用包含用逗号分隔的验证值列表的字符串，而使用指定列表存储范围的字符串.例如，如果您的验证列表存储在工作表"ValidationValues"中的A列中，从第2行到1001，则您的字符串将如下所示:

Instead of using a string that contains the list of validation values separated by commas, use a string that specifies a range where the list is stored. For example, if you validation list is stored on the worksheet "ValidationValues" in column A from row 2 to 1001, your string would look like this:

"=ValidationValues!A2:A1001"

每个验证值都必须位于其自己的工作表单元格中.

Each validation value needs to be in its own worksheet cell.

然后您可以将此字符串传递给CORE_SetValidation子项:

You could then pass this string in to the CORE_SetValidation sub:

Call CORE_SetValidation(myRange, "=ValidationValues!A2:A1001")

这篇关于VBA中的Excel验证下拉长度问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！