I got this problem run-time error 1004 application defined ...

[Davide Blau]

This is my code, when i try to add a xlValidateList in an excel sheet
i receive a run time error
1004 application defined or object defined error ...

I tried also to add other lists or formula inside that sheet, it's
possible i've to add something in my project (mmm ...) ??


Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Foglio1 As Worksheet

Dim PathDataBase As String
Dim list As String

'Store the path of the database
PathDataBase = Sheets("Foglio1").Range("E2").Value

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & PathDataBase & ";"

'now find in tblCostruttori and replace in the table
Set rs = New ADODB.Recordset
rs.Open "tblCostruttori", cn, adOpenKeyset, adLockOptimistic,
adCmdTable
lunghezza = rs.RecordCount
lista = ""
Do While (Not rs.EOF)
list = list & rs.Fields("Costruttori") & ";"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

With Sheets("Foglio1").Range("C2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=list
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

 

[mrice]

Which line does the macro get stuck on

 

[mrice]

Which line does the macro get stuck on