Excel validation list - runtime error 1004 - Application definition or object definition error

Question

Here’s how it works: I have an array that contains the items for a drop-down list in excel. The code just below creates drop-down lists on cells D5 through D108 on 12 month vector tabs. The expense vector contains the items for this drop-down list.

But on the given line gives the following error:

runtime error 1004 - Application definition or object definition error.

Dim mes(12) As String
mes(1) = "Janeiro"
mes(2) = "Fevereiro"
mes(3) = "Março"
mes(4) = "Abril"
mes(5) = "Maio"
mes(6) = "Junho"
mes(7) = "Julho"
mes(8) = "Agosto"
mes(9) = "Setembro"
mes(10) = "Outubro"
mes(11) = "Novembro"
mes(12) = "Dezembro"

Dim despesa(19) As String
despesa(1) = "ÁGUA"
despesa(2) = "CONTABILIDADE"
despesa(3) = "DESPESA FIXA"
despesa(4) = "DEVOLUÇÃO"
despesa(5) = "DIARISTA"
despesa(6) = "DIVERSOS"
despesa(7) = "ENERGIA"
despesa(8) = "IPTU"
despesa(9) = "MANUTENÇÃO"
despesa(10) = "MAT. CONSUMO"
despesa(11) = "MAT. CURSOS"
despesa(12) = "MAT. ESCRITÓRIO"
despesa(13) = "MAT. LIMPEZA"
despesa(14) = "MOVIMENTAÇÃO INTERNA"
despesa(15) = "SERVIÇOS GERAIS"
despesa(16) = "SITE"
despesa(17) = "TAXA BANCÁRIA"
despesa(18) = "TEL/INTERNET"
despesa(19) = "VIGILANCIA"

For i = 1 To 12

With Sheets(mes(i)).Range("d5:108").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, 
Operator:=xlEqual, Formula1:=Join(despesa, ",") 'erro nesta linha!!!!
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

    Next i

The thing is that so far I have not understood the schema of parameters and syntax for the validation method. If anyone can spend five minutes with me to give me a brief explanation I’ll be doubly grateful!

Whatever term you’ve misused, I’ll also thank you for any polite correction you can make!

Answer 1

Hello, friend. I thoroughly tested your code and now I was faced with the mistake, now I didn’t. I took, well more than 5 min, hehehehe...

The error occurs when the validation insertion is disabled, for some reason. In my case, I found that I was with more than one selected Worksheet, because, before running the code, I selected all spreadsheets and cleaned the contents of all simultaneously. That way the code impaled right on the line of the Validation.Add. However, by pressing Depurar, disable the multiple selection of Sheets, and resume code execution, everything works fine! I imagine, therefore, that you are doing something similar.