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
– Rich Holton, Commented Apr 17, 2017 at 11:43
syntax note: Call is deprecated and unnecessary. Just use .Delete and .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Value — ThunderFrame
– ThunderFrame, Commented Apr 17, 2017 at 12:30

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

1

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")

answered Apr 17, 2017 at 12:34

Rich Holton

6925 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ManiMuthuPandi Over a year ago

Can I get the total rows value dynamically from the sheet "ValidationValues" to form the above validation

Rich Holton Over a year ago

Yes, in various ways. See for example here: stackoverflow.com/questions/71180/…

ManiMuthuPandi Over a year ago

Great; Your Answer and comments gives me the good solution for me. But after saving the file I am closing it. Then when I reopen the saved file it gives this error "File error:data may have been lost."

Rich Holton Over a year ago

I'm sorry that you're having this problem. I don't know what would cause it. Are you able to tell what data was lost? Maybe you should open a new question so others can help.

ManiMuthuPandi Over a year ago

Thanks for your answer, One last question can I apply sorting to the created drop down using the range

Collectives™ on Stack Overflow

Excel Validation Drop Down length issue in VBA

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related