0

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

enter image description here

enter image description here

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. 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.
  2. Can I able to get the exact error message.
ManiMuthuPandi
  • 1,594
  • 2
  • 26
  • 46

1 Answers1

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")
Rich Holton
  • 662
  • 5
  • 12
  • Can I get the total rows value dynamically from the sheet "ValidationValues" to form the above validation – ManiMuthuPandi Apr 17 '17 at 13:30
  • Yes, in various ways. See for example here: http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro – Rich Holton Apr 17 '17 at 14:00
  • 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."** – ManiMuthuPandi Apr 17 '17 at 14:31
  • 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. – Rich Holton Apr 17 '17 at 14:58
  • Thanks for your answer, One last question can I apply sorting to the created drop down using the range – ManiMuthuPandi Apr 18 '17 at 09:54