Alimentare dei valori nelle celle di più fogli di lavoro da un unico elenco. - dal 04/09/04 pagina vista: volte

Ovvero: come assegnare valori a celle con selezione del valore preso da una ListBox, ed eliminazione del valore scelto dalla ListBox stessa.

Un esercizio non difficile nel suo insieme, ma che richiede come tutti i lavori che si impostano, l'analisi della situazione con impostazione di uno schema da seguire. L'esercizio di per se è fine a se stesso, cioè predisposto per una precisa applicazione, ma si presta altresì a molti altri utilizzi, che possono essere suggeriti da questa idea, e pertanto lo presento.

La situazione: una cartella di lavoro con più fogli tutti uguali nello schema tabellare, cioè i range di lavoro iniziano per tutti i fogli nelle medesime celle; in più, un altro foglio, che chiameremo "Master", dove risiedono dei dati, tra i quali una colonna con dei valori. Valori che ovviamente potrebbero essere di qualsiasi natura (testo, numeri, date, ecc.),  in questo esempio simuleremo che siano valori alfanumerici, e più precisamente dei codici che identificano "Gruppi di utenza". Vediamo uno dei fogli come esempio:

Cosa si vuole ottenere:

1. vogliamo poter disporre dell'elenco di questi codici sul foglio Master, direttamente su ogni foglio di lavoro

2. vogliamo poter inserire, in una cella di qualsiasi foglio di lavoro che non sia il "Master", in un determinato Intervallo della colonna A, e precisamente dalla A10 alla A49, (celle in verde nell'immagine sopra) un qualsiasi codice che decideremo di volta in volta, e che sarà presente in questo elenco "volante", selezionando prima la cella e quindi un codice che si troverà nell'elenco della ListBox.

3. vogliamo anche che una volta inserito questo codice in una cella, il codice stesso sparisca dall'elenco in modo che non sia più disponibile per nessun altro foglio di lavoro.

4. vogliamo anche che l'elenco con i codici compaia quando selezioniamo una cella del range A10:A49, e che questo avvenga qualunque foglio sia attivo in quel momento.

A fronte di tutto ciò, e al di la del fatto che si possono seguire diverse strade per ottenere ciò che vogliamo, ho scelto di impostare così il lavoro:

1. useremo una UserForm dove inserire una ListBox, la cui lista caricheremo con i dati che sono nel foglio Master relativi ai codici che vorremo poi selezionare. UserForm che dovrà apparire quando si selezionerà una delle celle del range predetto (A10:A49). Per questo sfrutteremo l'evento Workbook_SheetSelectionChange che si verifica quando si cambia la selezione su un qualsiasi foglio di lavoro, e ci serviremo del metodo Intersect per identificare le celle, selezionando le quali, si attivi solo in questo caso l'evento per far apparire la UserForm. Aggiungeremo anche un controllo condizionale perchè l'evento non si applichi se si seleziona la stessa area ma nel foglio Master. L'ultima cosa che riguarda la UserForm, ne imposteremo la sua proprietà ShowModal a False; questo ci consentirà di avere una userform fluttuante (floating), che si potrà spostare, si vedrà in primo piano anche cambiando foglio, e soprattutto ci consente di poter lavorare sui fogli, cosa impossibile se la proprietà fosse impostata a True. Questa modifica alla proprietà non è purtroppo possibile con la versione 97 di Excel.

2. nella ListBox caricheremo l'elenco voluto (quello che si trova sul foglio Master) usando il metodo AddItem, e sfrutteremo l'evento Activate della UserForm per caricarlo. Questo ci consentirà all' attivazione della UserForm, di disporre di tutto l'elenco completo dei codici.

3. Vorrei precisare che per quanto riguarda il punto 2 ed il punto successivo (4), si può decidere di operare in due modi diversi per quanto riguarda l'eliminazione delle voci che selezioneremo nella ListBox. Ci dobbiamo chiedere per quanto richiesto al punto C (inizio pagina) :

• caso 1 - vogliamo che una volta selezionata una voce, la stessa sparisca solo dalla Lista nella ListBox ?

• caso 2 - oppure vogliamo che sparisca anche la corrispondente voce nell'elenco sul foglio Master ?

4. Presento tutte e due questi casi. Proseguiamo ora esaminando il caso 1:  NON dovremo chiudere la UserForm una volta attivata, ma trascinarcela dietro su qualunque foglio si lavori, per evitare che la chiusura della form e successiva riapertura, ricarichi tutto l'elenco vanificando la cancellazione delle voci già selezionate. Per la cancellazione di una voce useremo il metodo RemoveItem, e inseriremo l'istruzione nell'evento Click di un CommandButton che inseriremo sulla Userform. Vediamo ora le routines:

Per prima la routine inserita nell'evento Workbook_SheetSelectionChange, e che ha il compito di definire l'area che reagirà al cambio di selezione (Intersect), per tutti i fogli tranne che per il foglio "Master", e che serve per aprire la UserForm :

Ora vediamo la routine inserita nell'evento di apertura della UserForm (Activate) e che si incarica di prelevare l'elenco dei valori dal foglio "Master", nell'intervallo A1: A200, e caricare la ListBox con il metodo AddItem:

Vediamo ora due immagini: la prima a sinistra mostra, appena si seleziona una cella nel range previsto del foglio di lavoro (la cella A10 in questo esempio), la immediata comparsa della userform; l'immagine a destra invece mostra la copia del valore selezionato nella ListBox nella cella attiva (A10), ottenuta con la pressione sul pulsante "Inserisci", dopo aver selezionato la voce nella ListBox, e la successiva scomparsa dello stesso valore. Risulterà poi selezionato il valore successivo a quello eliminato.

Vediamo quindi la routine inserita nell'evento Click del CommandButton, che sfruttando il metodo RemoveItem, cancella il valore che avremo selezionato, dalla ListBox:

Passiamo al caso 2 - Le procedure sono identiche per quanto riguarda gli eventi Workbook_SheetSelectionChange e UserForm_Activate, mentre modificheremo con due nuove istruzioni la routine inserita nel commandbutton. Poichè avremo bisogno di cancellare anche la voce nell'elenco sul foglio "Master" (corrispondente alla voce selezionata nella ListBox), ci potremo permettere di chiudere la UserForm dopo la copia del valore nella cella, tanto alla sua riapertura per una successiva selezione di una cella (zona in verde) verrà ricaricato l'elenco originario dove però mancheranno le voci cancellate, e quindi non più disponibili. Unica controindicazione: in questo modo distruggeremo tutto l'elenco contenuto nel foglio Master. Se volessimo invece scegliere questa seconda strada, ma non perdere definitivamente l'origine dei dati, potremmo lavorare su un elenco copia dell'originale; per esempio disporre il nostro elenco nella colonna C del foglio Master, aggiungere una macro con il compito di copiare l'elenco contenuto nella colonna C nella colonna A (intervallo che serve alla ListBox per "pescare" l'elenco) in modo da poter ripristinare l'elenco una volta distrutto dalla cancellazioni. Questa macro potremo attivarla manualmente tramite un pulsante, e decideremo noi il quando, oppure inserirlo nell'evento Workbook_Open e tutte le volte che riapriremo la cartella otterremo una copia dell'elenco, e questa potrebbe essere l'istruzione:  Sheets("Master").[A1:A200] = Sheets("Master").[C1:C200]

Vediamo ora la routine modificata da inserire nell'evento Click del commandbutton:

Buon lavoro.

prelevato sul sito www.ennius.altervista.org