Excel Validation Drop Down length issue in VBA

Question

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

Below is the screenshot

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

Is there any limit for characters or drop-down list in VBA drop-down, Because from the error message I am unable to predict the issue.
Can I able to get the exact error message.

Please copy and paste your code as text to make it part of your question. — Rich Holton
syntax note: Call is deprecated and unnecessary. Just use .Delete and .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Value — ThunderFrame

Rich Holton Rich Holton · Accepted Answer · 2017-04-17T12:34:08

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.

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

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

Excel Validation Drop Down length issue in VBA

1 Answers