Usare una maschera di consultazione/introduzione dati. - dal 04/09/04 pagina vista: volte

Abbiamo già visto in altri esercizi sui due siti, esempi di come usare una "maschera" per la consultazione e/o l'inserimento dati in una tabella o database che dir si voglia. Come "maschera" veniva usata una UserForm, nella quale, con opportune istruzioni, sfruttavamo TextBox sia per ricercare dati già esistenti in una tabella, sia per introdurne di nuovi.

Ma come "maschera" possiamo tranquillamente usare anche delle celle del foglio di lavoro, che siano destinate allo scopo, cioè celle i cui riferimenti restino fissi nel tempo.

In genere per ottenere una "maschera" di consultazione dati impostata su celle di un foglio, sfruttiamo una cella dove scrivere un dato, che rappresenterà il criterio di ricerca, ed altre celle (tante quanti saranno i dati correlati al criterio di ricerca) per inserire formule, come il CERCA.VERT, che ci restituiscano in quelle celle i dati correlati al criterio.

I metodi che useremo per scrivere o reperire il criterio di ricerca vanno dall'inserimento manuale di un dato, all'impiego di strumenti come Convalida, oppure ComboBox o ListBox che "peschino"  la lista dei dati nel "campo" (colonna) che useremo come criterio di ricerca, nel database esistente. Poco importa, in fase di progettazione del lavoro, se il database sarà vuoto di dati, l'importante però sarà aver predisposto una zona con i campi (colonne) necessari a contenere i dati, formattando le celle sul "tipo di dati" che dovranno contenere.

Di esempi su "maschere di consultazione" di questo tipo, ne trovate molte, in vari articoli su questi siti, ma questo esercizio si differenzia perchè vorremo utilizzare la stessa "maschera" (le stesse celle) anche per l'inserimento di nuovi dati nel caso che il criterio di ricerca non venga trovato nel database.

E qui ci scontriamo con una difficoltà:

• le celle che devono reperire i dati correlati al criterio di ricerca, ospiteranno funzioni come il CERCA.VERT, oppure SCARTO, oppure INDICE, o altre ancora.

• quindi se vorremo invece digitare nelle stesse celle nuovi dati, automaticamente cancelleremo le formule residenti, con il risultato che alla successiva ricerca, mancando le formule, anche se il criterio esiste, non otteremo i dati correlati.

Diventa chiaramente impensabile dover riscrivere, dopo ogni nuovo inserimento, tutte le formule; potremo però usare il vba, che come sempre ci viene in aiuto.

Quando si usano "maschere" di lavoro, ci troviamo spesso nella condizione (volendo registrare poi i dati immessi) di avere una parte di dati variabili, che cambieranno continuamente, ad esempio nella formazione di una fattura, cambieranno sicuramente le quantità e quindi gli importi parziali e i totali, mentre molto spesso un'altra parte di dati potranno essere ripetitivi, come i dati Cliente, visto che potremo ad uno stesso cliente emettere più fatture, o gli articoli venduti; in questo caso è preferibile disporre di un sistema di reperimento dati che ci consenta di ottenere, nelle celle opportune, i dati correlati ad esempio ad un nome cliente o alla sua partita iva se già esistente nel database di pertinenza, e usati come criterio di ricerca. Ci avviciniamo agli esempi tema di questo articolo.

Prenderemo in considerazione solo una parte dei dati che andranno registrati, cioè i dati che potranno essere ripetitivi, visto che solo questa parte di dati rientra nella necessità dell'esercizio : usare le stesse celle per ottenere dati correlati ad un criterio di ricerca, oppure inserirne di nuovi usando le stesse celle. Vediamo l'immagine di una ipotetica "maschera", e il database che ospiterà i dati: sul Foglio1, le celle in giallo sono le celle che formano la "maschera"

e questa l'area di registrazione dati (tabella database) sul Foglio2, tabella che servirà sia per la ricerca del criterio di ricerca, (useremo il numero di partita iva, che sicuramete è univoco) che per la registrazione dei dati (o valori che dir si voglia) ottenuti o scritti nelle celle gialle sul foglio1:

Chiaramente i dati sono di fantasia.

Divideremo le istruzioni sulla base delle necessità:

• eseguire una ricerca per verificare se in numero di partita iva è già presente nel database, e in questo caso riempire la "maschera" con i dati correlati alla partita iva.

• registrare i dati comunque ottenuti nella "maschera" nel database cercando la prima riga libera dove inserirli.

Per il primo punto ho preparato due diverse soluzioni: la prima si avvale dell'impiego di "Convalida" per reperire la partita iva, andando a leggere la colonna B del foglio2, la seconda attraverso l'inserimento manuale di una partita iva nella cella F9. Vediamole entrambe.

• Usando Convalida:

Intanto ricordo che per destinare come "elenco" un'area posta su un foglio diverso da quello su cui risiede Convalida, è necessario assegnare un "nome" (dal Menù Inserisci/Nome/Definisci) all'area, e richiamare questo "nome" come origine elenco; infatti Convalida si rifiuta di accettare ad esempio =Foglio2!B1:B2000 perchè l'area di provenienza è su un foglio diverso. Se avremo usato come "nome" : "piva", dovremo scrivere in Convalida: =piva, e l'elenco sarà accettato. Quando Convalida è inserito in una cella, funziona simile ad una ComboBox: cliccando la cella che lo contiene, apparirà il classico menù a discesa dal quale potremo selezionare una voce dell'elenco, e questa voce diventerà la voce visibile. Ho usato la cella F1, avendo cura di usare Blocca Riquadri sulla seconda riga, in modo da avere un'area sempre in vista; vediamo un'imagine:

Quando selezioniamo un dato in Convalida, noi provochiamo un cambiamento di valore nella cella, e quindi attiviamo l'evento Change sul foglio di lavoro; sfrutteremo quindi questo evento per inserire le opportune istruzioni. Per evitare che le istruzioni siano attivate ad ogni cambiamento del foglio, useremo il metodo Intersect per lìimitare l'azione solo se sarà la cella F1 a subire il cambiamento. Poi, anche per diversificare il tipo di istruzioni in modo da offrire esempi diversi, useremo in questo caso l'inserimento, tramite vba, di formule CERCA.VERT (in inglese VlookUp) nelle celle della maschera, con le istruzioni che ogni cella vada a pescare il dato correlato alla partita iva, ma nel campo specifico.

Otteniamo in questo caso che le formule verranno generate ad ogni cambio in Convalida, e poco importa se, non trovando il criterio presente nel database, cancelleremo le formule nel momento in cui scriveremo i nuovi dati. Questa procedura ci garantisce quindi il costante utilizzo delle formule al momento giusto. Potevamo scegliere la strada di far cercare via codice i valori correlati e riempire in questo modo le celle, ma volendo offrire più possibilità, in questo esempio ci comportiamo così. Vedremo un'altro modo nella seconda soluzione, e ognuno poi sarà libero di modificare anche questo esempio: questa la routine onserita nell'evento Change dei Foglio1

E questo è uno dei modi che possiamo usare. Ora vediamo invece la seconda soluzione.

• Inserimento manuale di una partita iva nella cella F9

Anche in questo caso sfrutteremo l'evento Change del Worksheet, che si verificherà quando, scritto il numero nella cella, premeremo il tasto "invio" per confermare. Stesso discorso per Intersect, mentre in questo caso useremo un ciclo di ricerca che sul foglio2, nella colonna B vada a cercare un valore uguale a quello appena scritto in F9. Se la ricerca risulterà positiva, sfrutteremo Offset per reperire i valori dei campi correlati da inserire nelle rispettive celle gialle sul foglio1.

Ed ora che avremo i dati nelle celle della "maschera", in un modo o nell'altro, dobbiamo pensare ad aggiungere questi dati nel database archivio sul foglio2. Infatti  solo dopo che avremo ottenuto i dati che ci interessano, che potremo "copiarli" a destinazione.

• Copia dei dati raccolti, sul foglio destinatazione (database archivio).

E' necessario un passaggio intermedio, prima di effettuare copia/incolla; disponiamo di dati, MA POSTI su celle non contingue e soprattutto non sono tutte sulla stessa riga; dovremo usare una "zona di raccolta" che "componga" su un'unica riga ed in celle conseguenti, i dati delle celle gialle. Non ha importanza dove collocheremo questa zona, l'importante e che sia, ripeto, su unica riga con celle adiacenti; per ottenere in questa zona gli stessi dati delle celle gialle, useremo una semplice formula di = (uguale). Nell'esempio uso le celle da B2 a F2, e comporrò le celle con la stessa sequenza corrispondente alla sequenza dei campi del database di destinazione sul foglio2, il perchè è ovvio e non lo spiego davvero. Comunque questa sarà la situazione una volta che avremo inserito i dati nelle celle gialle:

Come si vede nell'area verde chiaro, abbiamo composto le celle con la stessa sequenza dei campi del database, in B2 c'è la formula =F9, in C2 la formula =C11, ecc.

L'area verde chiaro è la zona che copieremo e che incolleremo a destinazione, usando il metodo PasteSpecial Solo valori, infatti con Copy verrebbero copiate anche le formule, e non andrebbe assolutamente bene. Ricordo che nell'incollare aree provenienti da più celle conseguenti, è necessario identificare solo la cella iniziale dove incollare; sarà Excel infatti a riempire tante colonne quante sono le colonne copiate. Quindi sfrutteremo un ciclo While che cercherà nella colonna B la prima riga liberà, e lì incolleremo i dati. Vediamo la Routine (associata ad un pulsante):

L'esercizio è finito, chiaramente vuole essere solo un aiuto per capire come impostare un lavoro, ma soprattutto assimilare i ragionamenti che ci aiuteranno a trasferire gli esempi visti in applicazioni per noi necessarie, e che da qui, è impossibile prevederle tutte considerandone le molteplicità.

Buon lavoro.

prelevato sul sito www.ennius.altervista.org