Convalida in VBA con Elenchi dinamici.  - pagina vista: volte

(requisiti: richiesta la conoscenza dell'uso di Convalida (Validate in vba))

L'argomento di questo articolo esiste già su questo sito (vedi :"Assegnare nomi ad Intervalli") dove si esemplifica l'uso di intervalli dinamici per definire un origine dati da assegnare a ListBox o ComboBox, attraverso l'assegnazione di un "nome" all'intervallo, (vedi in particolare ultima parte dell'articolo citato, che inizia il paragrafo con "..Ora vediamo un esempio in cui sia necessario rendere "variabile" l'intervallo assegnato ad un "nome"..".) e dove i concetti espressi si possono applicare anche all'uso di Convalida impostato appunto a "elenco".

Vediamo comunque il problema alla base di questo nuovo articolo:

".. Vorrei creare in una cella un menu a discesa con la convalida, ma invece di riferimenti fissi dell'elenco vorrei che fosse un elenco dinamico, che si aggiorna automaticamente. Per es.nella cella A1 ho il menu "a discesa" creato con Convalida (elenco) con riferimento ai dati inclusi in B1:B5. Invece di B5 vorrei che arrivasse a fine elenco..."

Il problema quindi è: come aggiornare il riferimento a elenco di Convalida quando si aggiunge una nuova voce, o si modifica una esistente?

Tra i vari sistemi che possiamo usare per reperire i limiti di un'intervallo, usiamo quello di assegnare un "nome" ad un intervallo di origine anche manualmente (dal menù Inserisci/Nome/Definisci).

Dovremo poi scegliere un'evento del foglio di lavoro, che attivi le necessarie istruzioni; l'evento più consono è il Worksheet_Change, che si verifica quando appunto avviene un cambiamento in una qualsiasi cella del foglio stesso.

Poichè l'azione si dovrà verificare solo quando interveniamo sull'elenco di origine di Convalida, useremo il Metodo Intersect (vedi art. sul sito) per limitare l'intervento solo se modifichiamo un preciso intervallo; non sapendo a priori quanto potrà diventare lungo l'elenco di origine, useremo l'istruzione su tutta la colonna B ( Range("B:B") ).

Riepiloghiamo quindi i passi da fare:

  • Assegnare un "nome" ad un'intervallo di origine, manualmente o tramite vba. (supponiamo il nome "pippo")

  • Predisporre una routine che, contando le righe dell'intervallo definito con un "nome", verifichi ad ogni cambiamento sul foglio se ci sono differenze nella lunghezza del nuovo intervallo (B:B): se ci saranno differenze (in più o in meno), sovrascriviamo il vecchio "nome" (che identifica appunto il precedente intervallo) ricreandolo con il nuovo intervallo modificato.

  • Predisporre nell'evento Worksheet_Change la chiamata della precedente ruotine se un cambiamento si verifica nell'area definita con Intersect.

Le Routine:

Sub AggiornaElenco()
Dim M, T As Integer
M = ActiveWorkbook.Names("pippo").RefersToRange.Rows.Count 'contiamo le righe nell'intervallo "pippo"
T = ActiveSheet.Range("B1").End(xlDown).Row 'contiamo, cercando l'ultima riga occupata, le righe nell'intervallo 'che inizia 'da B1, sul foglio attivo. non sono ammesse celle vuote tra i valori dell'elenco.
If M <> T Then 'se M è diverso da T, ricreiamo il nome sovrascrivendolo col nuovo intervallo (sotto : )
ActiveWorkbook.Names.Add Name:="pippo", RefersTo:=Range("B1:B" & T)
End If
End Sub


 

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then
Exit Sub
Else
AggiornaElenco     'lanciamo la macro predisposta su un modulo standard
End If
End Sub


Queste istruzioni aggiornano in tempo reale Convalida, in qualunque cella la sia posta.
 

prelevato sul sito www.ennius.altervista.org