#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

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. 'L'intervallo da validare, ha ancora attiva la "Convalida dati"?
  3. If HaValidazione(Range("IntervalloDaValidare")) Then
  4. Exit Sub
  5. Else
  6. MsgBox "La tua ultima operazione è stata annullata. " & _
  7. "Avrebbe eliminato la convalida dei dati.", vbCritical
  8. Application.Undo
  9. End If
  10. End Sub
  11.  
  12. Private Function HaValidazione(r) As Boolean
  13. 'Restituisce TRUE se ogni cella nell'intervallo r usa la convalida dati
  14. Dim rng As Range
  15. Dim c As Range
  16. Dim val As Long
  17. Dim val2 As Long
  18.  
  19. HaValidazione = False
  20.  
  21. Set rng = r
  22.  
  23. val2 = 0
  24. For Each c In rng
  25. val = 0
  26. On Error Resume Next
  27. val = c.SpecialCells(xlCellTypeSameValidation).Count
  28. val2 = val2 + val
  29. On Error GoTo 0
  30. Next
  31.  
  32. If val2 = r.Cells.Count Then
  33. HaValidazione = True
  34. Else
  35. HaValidazione = False
  36. End If
  37.  
  38. Set c = Nothing
  39. Set rng = Nothing
  40. Set sh = Nothing
  41.  
  42. 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.

LEGGI:  #Excel - Come obbligare l'utente ad immettere dati con ordine [Quick Tip]

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:

Messaggio per l'annullamento dell'ultima azione
Figura 1 – Se la convalida dati è stata eliminata l’ultima azione verrà annullata

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.

Copyright © 2014-2017  Office Academy. Tutti i diritti riservati.
Vai alla barra degli strumenti