#Excel – Come neutralizzare il “tallone d’Achille” della Convalida dati [VBA]

Chi usa regolarmente la “Convalida dati” per limitare quello che può essere inserito in una cella, avrà sicuramente già notato che, purtroppo, è sempre possibile sovrascrivere i dati validati copiando una cella da ovunque vogliamo e incollandola nella cella che dovrebbe essere limitata dalla “Convalida dati”.

Questo è il vero “tallone d’Achille” della Convalida dati: è facile per un utente (accidentalmente o intenzionalmente) eliminare le regole di validazione. Ad esempio, copiando un intervallo di celle e poi incollandolo in un intervallo che contiene la convalida dei dati: la validazione dei dati sarà distrutta.

L’unico modo possibile per ovviare a questo problema è quello di utilizzare una macro in modo da scongiurare l’incollatura in alcune zone del foglio di lavoro. Un esempio di una soluzione simile potrebbe essere questo:

1. Impostate la “Convalida dati” come al solito
2. Selezionate tutte le celle che utilizzano la “Convalida dati” e dategli il nome “IntervalloDaValidare”. Questo può essere composto sia da celle contigue, sia da celle non contigue
   IMPORTANTE

   Assicuratevi che ogni cella di “IntervalloDaValidare” contenga la “Convalida dati”.
3. Inserite il codice seguente nel modulo del foglio di lavoro (ad esempio se il vostro foglio di lavoro si chiama Foglio1, il codice andrà inserito nel modulo chiamato Foglio1). Le modalità per l’inserimento di codice all’interno di un foglio di lavoro sono state più volte spiegate in altri articoli riguardanti il VBA

Private Sub Worksheet_Change(ByVal Target As Range)
    'L'intervallo da validare, ha ancora attiva la "Convalida dati"?
    If HaValidazione(Range("IntervalloDaValidare")) Then
        Exit Sub
    Else
        MsgBox "La tua ultima operazione è stata annullata. " & _
        "Avrebbe eliminato la convalida dei dati.", vbCritical
        Application.Undo
    End If
End Sub

Private Function HaValidazione(r) As Boolean
'Restituisce TRUE se ogni cella nell'intervallo r usa la convalida dati
    Dim rng As Range
    Dim c As Range
    Dim val As Long
    Dim val2 As Long
     
        HaValidazione = False
        
        Set rng = r
        
        val2 = 0
        For Each c In rng
            val = 0
            On Error Resume Next
            val = c.SpecialCells(xlCellTypeSameValidation).Count
            val2 = val2 + val
            On Error GoTo 0
        Next
        
        If val2 = r.Cells.Count Then
            HaValidazione = True
        Else
            HaValidazione = False
        End If
     
    Set c = Nothing
    Set rng = Nothing
    Set sh = Nothing

End Function

Come funziona

La procedura Worksheet_Change viene eseguita ogni volta che una cella, o un intervallo, si modifica sul foglio di lavoro. Il codice chiama la funzione “HaValidazione” per garantire che l’intervallo denominato “IntervalloDaValidare” contenga ancora la convalida dei dati. Se ogni cella in tale intervallo contiene la convalida dei dati, la funzione restituisce TRUE, la procedura termina e non viene eseguita alcuna azione.

Se una o più celle nel IntervalloDaValidare non contengono più la convalida dei dati, la funzione restituisce FALSE. Ciò significa che l’utente ha eliminato la convalida dei dati in una o più celle. In tal caso, l’ultima operazione viene annullata, e l’utente vede il messaggio che segue:

Alcuni punti di attenzione

Questa procedura riesce ad intercettare le seguenti operazioni, che normalmente eliminano le regole di convalida dei dati:

• taglia/incolla di dati
• copia/incolla di dati
• il comando HOME > Modifica > Cancella tutto

Tuttavia, non viene rilevata l’eliminazione di righe o colonne che contengano la convalida dei dati.

In aggiunta, è bene che sappiate che:

• Se l’utilizzatore del foglio di lavoro non attiva le macro quando la cartella di lavoro viene aperta, il codice non potrà fare il suo lavoro (questo è, onestamente, uno svantaggio delle macro in generale)
• Il foglio di lavoro potrebbe sembrare un po’ lento se l’intervallo di celle da validare è grande, in quanto la macro viene eseguita ogni volta che viene apportata una modifica al foglio
• Poiché la macro viene eseguita ogni volta che il foglio di lavoro subisce una variazione, rimuove di fatto qualsiasi possibilità di impartire il comando “Annulla” (o Ctrl + Z), in quanto una macro, quando viene eseguita, non può essere annullata e cancella lo stack di annullamento.

Tuttavia, anche con i suoi inconvenienti e le sue limitazioni, questo approccio è probabilmente il modo migliore per superare il potenziale problema.

Se preferisci, un’altra possibile soluzione è illustrata in questo articolo della nostra Area Premium.