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

Question

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

Navigation

#1 by (0 votes)

1

The deal is like this: I have a vector that contains the items for a dropdown list in excel. The code just below creates hanging lists in cells d5 through d108 in 12 tabs of the month vector. The expense vector contains the items for this drop-down list.

But in the line indicated it gives the following error:

run-time 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 business is that I have not understood the scheme of the parameters and the syntax for the validation method so far. If anyone can spend 5 minutes with me to give me a brief explanation I will be doubly grateful!

Any term you have used wrongly, I will also thank you for any polite correction you can make!

excel vba

asked by anonymous 18.04.2017 / 18:54

1 answer

Publish in playstore does not hit apk name Why in OpenGL do we need to transform all objects to render them?

score 0 · Accepted Answer

Hello, my friend. I have thoroughly tested your code, and now I was faced with the error, not now. It took me, well over 5 min, hehehehe ...

The error occurs when the validation insert is disabled for some reason. In my case, I came across that I had more than one worksheet selected, because before running the code, I selected all the worksheets and cleaned the contents of all of them simultaneously. That way the code packed well in the Validation.Add line. However, by pressing Depurar , disable multiple sheet selection, and resume code execution, everything works fine! I imagine, therefore, that you are doing something similar.