[EXCEL2010] Impostazione convalida dati tramite VBA

[pl1957]

Buona serata a tutti.

Devo inserire tramite VBA una formula di convalida per una cella.
La formula da inserire è:

Codice: Seleziona tutto

=SE(B1<>"";CONFRONTA(B1;$D$1:$D$3;0)>0)

Studiando il registratore di macro e facendomi un giretto con Google, ho elaborato questa routine:

Codice: Seleziona tutto

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim s As String
    If Target.CountLarge > 1 Then Exit Sub ' Evito selezioni multiple
    If Target.Row = 1 And Target.Column = 2 Then
        s = Chr(34) & "=SE(B1<>" & Chr(34) & Chr(34) & Chr(34) & Chr(34) & ";CONFRONTA(B1;$D$1:$D$3;0)>0)" & Chr(34)
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & s
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = " Errore!"
            .InputMessage = ""
            .ErrorMessage = "Valore non previsto."
            .ShowInput = True
            .ShowError = True
        End With
    End If
End Sub

Ho dovuto aggiungere una coppia di doppi apici (Chr(34)) ad inizio e fine della impostazione della variabile s in quanto, in caso contrario, ottengo:

Codice: Seleziona tutto

Errore di run-time '1004':
Errore definito dall'applicazione o dall'oggetto

con l'evidenziazione gialla del debugger posizionata in corrispondenza dell'istruzione .Add.

Il problema è:
Se imposto a mano la formula escludendo il VBA (ho inserito un "EXIT SUB" come prima istruzione) la convalida funziona perfettamente.
Se la imposto tramite VBA, qualsiasi valore inserisca ottengo una mancata convalida.
Impostandola tramite VBA e puntando alla cella B1 e controllando la formula di validazione, riscontro che questa è esattamente quella che voglio.

Qualche idea?

Non guardate al fatto che la cella dove applicare la validazione sia una e la matrice dove effetuare la ricerca per CONFRONTA sia così corta, perché prima di andare sul reale, con una sessantina di celle in cui impostare una validazione che sarà dinamica e con le matrici troppo lunghe per ricorrere ad una validazione tramite elenco, mi sto studiando la cosa in piccolo.

Excel 2010 su Win 7.

[Flash30005]

Ma non capisco cosa intendi per convalida
Convalida in Excel permette ad una cella di creare un elenco o una serie di dati da selezionare tramite menu a discesca
è questo che vuoi?

ciao

[pl1957]

No. Intendo quello che ho scritto.
Se la funzione CONFRONTA() restituisce un valore maggiore di ZERO allora il dato è valido e l'elaborazione prosegue, altrimenti viene restituito un messaggio di errore.
Come ho scritto, l'eventuale casella a discesa è improponibile stante l'elevato numero di valori.

La formula di convalida funziona perfettamente se inserita a mano, ma se la stessa formula la voglio inserire tramite il VBA con la macro che ho registrato, mi dà sempre risultato negativo.

In altre parole:
Non chiedo aiuto per scrivere la formula, chiedo aiuto per riuscire ad impostarla tramite VBA.
Che è tutt'altra cosa.

Grazie.

OT: Ma che è successo? Ci avete fatto prendere un colpo quando per un paio di giorni si otteneva l'errore 404 tentando di collegarsi...

[Anthony47]

Lavorando dal vba devi usare le formule in inglese, quindi:

Codice: Seleziona tutto

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim s As String
If Target.Address = "$B$1" Then
    s = "=if(B1<>"""",Match(B1,$D$1:$D$3,0)>0)"
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=s
            .IgnoreBlank = True
'etc etc

Ciao

[pl1957]

Lavorando dal vba devi usare le formule in inglese, quindi:

Funzia... Graaassssssieeee!