VBA - Max characters in the dropdown-list

Question

VBA - Max characters in the dropdown-list

Navigation

#1 by (2 votes)

1

' Validação Motivo
Columns(motivo).Select
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= _
    "TESTE1;TESTE2;TESTE3;TESTE4;TESTE5;TESTE6;TESTE7;TESTE8;TESTE9;TESTE10;TESTE11;TESTE12;TESTE13;TESTE14;TESTE15;TESTE16;TESTE17;TESTE18;TESTE19;TESTE20;TESTE21;TESTE22;TESTE23;TESTE24;TESTE25;TESTE26;TESTE27;TESTE28;TESTE29;TESTE30;TESTE31;TESTE32;TESTE33"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

If my list of 'tests' exceeds 256 characters, my list gets blocked and if it exceeds 1024, an error is received.

In the example above, I have a list up to test33, totaling 256 characters. How would I add the test34 and thus circumvent this error?

excel vba excel-vba excel-userform

asked by anonymous 30.11.2017 / 18:10

1 answer

Passing a variable in a modal bootstrap ChatBot PHP - Facebook Messenger

score 2 · Accepted Answer

Referring to this Global OS response: Maximum drop-down list / formula length in Excel

The step-by-step will be explained below:

Create Spreadsheet with Validation Data

First a worksheet should be created with the validation data, not the mode:

Formula1:= _ "TESTE1;TESTE2;TESTE3;TESTE4;TESTE5;TESTE6;TESTE7;TESTE8;TESTE9;TESTE10;TESTE11;TESTE12;TESTE13;TESTE14;TESTE15;TESTE16;TESTE17;TESTE18;TESTE19;TESTE20;TESTE21;TESTE22;TESTE23;TESTE24;TESTE25;TESTE26;TESTE27;TESTE28;TESTE29;TESTE30;TESTE31;TESTE32;TESTE33"

The Name% wrapper must be created and the validation data placed in some column, in the example in Column A, as in the image:

Code

ThecodebelowinsertsthelistofColumnAoftheListValidworksheetasvalidationincolumnCoftheWorksheet1worksheet.

LastRow=Worksheets("ListaValid").Cells(Rows.Count, "A").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="List", RefersTo:="=ListaValid!$A$1:$A$" & LastRow
Worksheets("Planilha1").Range("C:C").Validation.Delete
Worksheets("Planilha1").Range("C:C").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=List"

Note: To avoid blank cell errors in the validation list, a function to delete this data can be added.

Result

A test was performed with the validation list with data from TESTE1 to TESTE400, that is, 400 lines.

The result is as follows:

Explanation

Lastline

ListaValid

FindthelastrowoftheworksheetfromthevalidationlistandcolumnA,thatis,thelistdatamustbefilledinfromline1.

CreateNameList

LastRow=Worksheets("ListaValid").Cells(Rows.Count, "A").End(xlUp).Row

Creates a List with the data from the validation worksheet, in Range A1 through A & Last Line

Delete Validation

ActiveWorkbook.Names.Add Name:="List", RefersTo:="=ListaValid!$A$1:$A$" & LastRow

Delete all validation from column C, to insert it in the next line and avoid errors.

Enter Validation

Worksheets("Planilha1").Range("C:C").Validation.Delete

Inserts the validation with the data from the created list.