Usare RefEdit (oggetto ActiveX). - dal 04/09/04 pagina vista: volte

In questo esercizio ci occuperemo dell'"oggetto" RefEdit, uno strumento utile in programmazione, che potrà interessare, credo, diversi utilizzatori di vba.

Quante volte si impazzisce per creare riferimenti variabili (cioè definibili di volta in volta, a piacere), riferimenti che debbano servire per determinare un'origine dati da utilizzare nelle nostre procedure?

Quante volte vogliamo identificare celle, con i cui valori vogliamo realizzare altre tabelle, una sorta di "seleziona e copia"? una specie di Filtro che ci consenta di riunire valori (dati) in modo da comporre una nuova tabella?

Faremo un esempio in cui, utilizzando RefEdit, selezioneremo aree dati in un nostro ipotetico database, per caricare con i dati di quest'intervallo una ListBox. Ipotizzeremo anche, che per ogni valore così caricato nella ListBox, si possa ottenere la copia del valore che selezioneremo, in un'altra zona, magari di un altro foglio di lavoro.

Intanto vediamo come ottenere il RefEdit :  è un componente ActiveX, che possiamo chiamare nell'editor di visual basic, inserendo prima una UserForm, e dalla sua casella degli strumenti, selezionare cliccandoci destro, la voce "Controlli aggiuntivi". Nella finestra che appare, e che mostra tutti gli "oggetti ActiveX" diponibili sul proprio computer, bisogna mettere un segno di spunta alla voce : RefEdit.Crtl corrispondente alla libreria Refedit.dll normalmente disponibile all'interno della cartella Office in C:\Programmi\Microsoft Office\OfficeX\Refedit.dll (dove X sarà la vostra versione di Office).

Nella immagine sotto vediamo come appare il controllo RefEdit nella casella degli strumenti della UserForm (freccia rossa), e come poi come si vedrà sulla UserForm (freccia blu) dopo che avremo selezionato il controllo e lo avremo trascinato, per dimensionarlo. Assomiglia ad una ComboBox, ma svolge funzioni molto diverse:

Il RefEdit è lo strumento che consente di inserire un riferimento ad una o più celle, mediante il mouse, in modo simile a quanto accade con molte funzionalità di Excel, come in Ricerca obiettivo, Formattazione condizionale, Inserisci funzione per reperire i riferimenti alle celle (gli argomenti), ecc.ecc.  insomma quella semplice finestrina lunga, che appare quando per fornire un riferimento clicchiamo sull'icona a destra dell'editor di riferimenti (o RefEdit che dir si voglia), quella specie di textbox che vediamo indicato nell'immagine sotto, dalla freccia rossa:

e che fa scomparire la finestra, lasciando al suo posto questa finestrina che vediamto sotto:

Quando ci troviamo in questa fase, se noi selezioniamo col mouse una cella o un intervallo, nella finestra appare l'intervallo selezionato, così:

La freccia rossa indica un'intervallo selezionato, e memorizzato, e poi agendo sull'icona a destra (freccia blu), ricomparirà la nostra finestra per la prosecuzione delle operazioni.

Credo che tutti avranno almeno una volta provato ad adoperare questo pratico sistema che Excel ci mette a disposizione.

Bene, riprendendo l'esempio di questo esercizio, potremo quindi usare la nostra UserForm con alloggiato un RefEdit ed una ListBox, sfruttare il RefEdit per "caricare" di volta in volta intervalli di celle che formeranno il RowSource della ListBox; questo ci consente di ottenere liste di dati variabili, che potremo usare per copiare, con un click sul dato che interessa nella ListBox, in altre zone dello stesso foglio, o "mirare" a celle o intervalli di altri fogli. Potremo creare quindi tabelle di riepiloghi di nomi, numeri, totali, date, orari, dipenderà ovviamente dal tipo di dato di origine.

Le modalità operative, dipenderanno da ciò che vogliamo ottenere : sarà possibile aggiornare dei valori, oppure inserendo un ciclo While, scegliere la prima riga libera di una colonna per accodare i dati ottenuti dalla selezione nella ListBox; oppure sarà possibile impostare routine che ci facciano scegliere di volta in volta la colonna (o la cella) di destinazione.

Vediamo da vicino come poter procedere; per prima cosa una precisazione: è necessario che la proprietà ShowModal della UserForm che useremo, sia lasciata con l'impostazione di default, cioè = True. Modificando questa impostazione avremo solo il blocco totale di Excel. Questo in contrapposizione a quanto contenuto nella guida il linea, che a proposito del Controllo RefEdit, recita:

  • "Non è possibile utilizzare un controllo RefEdit su un form utente a scelta obbligatoria (cioè ShowModal = True). È possibile utilizzare la proprietà ShowModal per impostare un form utente sull'opzione non a scelta obbligatoria. (cioè ShowModal = False)"

Evidentemente si tratta di un errore, visto che tutte le prove da me eseguite confermano quanto da me precisato. Ognuno comunque è libero di fare le proprie verifiche.

Il RefEdit quindi ci restituisce un riferimento a cella/celle selezionate, sul tipo di Foglio1!A1:A7, e ce lo restituisce come stringa; quello stesso tipo di stringa che serve ad assegnare l'intervallo di origine dati ad una ListBox (ma anche per ComboBox) assegnato alla proprietà RowSource (se su userform, altrimenti ListFillRange se su foglio di lavoro, ma attenti: il RefEdit non è disponibile come "oggetto" inseribile sul foglio, solo su userform).

Se inseriamo l'istruzione ListBox1.RowSource = RefEdit1.Text nell'evento Change del RefEdit, otterremo che ad ogni nuova selezione di celle tramite il RefEdit, assegnamo una nuova origine dati alla ListBox, aggiornando i dati nella lista.

Vediamo una sequenza di immagini:

l'UserForm con i due controlli, cliccando sul RefEdit... la userform si "raccoglie" e mostra solo il RefEdit...

 

selezionando un'area il RefEdit "raccoglie" i riferimenti, e premendo di nuovo a destra.... riappare la userform intera con la ListBox caricata con i dati presenti nell'intervallo selezionato.

Abbiamo infatti generato l'evento Change del RefEdit attivando le istruzioni viste sopra che caricano i dati presenti nell'intervallo selezionato. Quello che poi faremo con questi dati si presta a numerose variazioni. Ho preparato una routine che sfruttando un ciclo While, copierà ogni dato che selezioneremo nella Listbox, sul foglio2, a partire dalla riga 5, nella colonna C, cercando per ogni dato, la prima riga libera per inserire il dato stesso. Chiaramente le destinazioni sono liberamente assegnabili, dipenderà dalle esigenze di ognuno.

Precisazione: i dati caricati nella ListBox, anche se noi li vediamo col formato di origine,  sono "visti" dal codice senza riconoscimento del "tipo di dato" di provenienza (ma, per le date o gli orari,  solo come numeri; ricordo che Excel, per esempio, una data la vede come un numero che identifica quanti giorni sono trascorsi dalla data di inizio del suo calendario; altrettanto farà con gli orari (vedi articoli su date e orari sull'altro sito).

Questo comporta che se cliccassimo sulla data 31/12/03 presente nella ListBox dell'esempio, il valore che sarebbe trasposto è 37986 (che sono i giorni trascorsi dall'inizio calendario di excel) se il formato cella destinazione è impostato a "Generale", mentre verrà copiata come data se avremo predisposto, per le celle destinazione, il giusto formato cella come "Data"; lo stesso faremo se le celle destinazione dovranno ospitare "tipi di dato" specifici (orari, numeri con decimali, ecc).

Ricordiamoci sempre di questa necessità di predisporre il formato celle, sia in quelle di origine, sia in quelle di destinazione che ospiteranno lo stesso "tipo di dato" d'origne. Vediamo quindi le due routine: quella per caricare la ListBox:

  • Private Sub RefEdit1_Change()
    ListBox1.RowSource = RefEdit1.Text
    End Sub

e quella inserita nell'evento Click della ListBox, che serve appunto a copiare il dato ivi selezionato:

  • Private Sub ListBox1_Click()
    Dim iRow As Integer
    iRow = 5    
     'iniziamo dalla riga 5
    While Sheets(2).Cells(iRow, 3) <> "" 
    'cerchiamo una riga vuota sul foglio2, colonna 3 (la C)
    iRow = iRow + 1
    Wend
    miodato = ListBox1.Value     
    'con "miodato" prendiamo il valore selezionato nella listbox
    Sheets(2).Cells(iRow, 3) = miodato  
    'e lo copiamo nella cella trovata libera
    End Sub

Questa routine serve ad accodare dati nella colonna di destinazione, se volessimo copiare un dato in una cella fissa, magari per aggiornare la destinazione con valori che vengano da celle diverse, basterà un'istruzione più semplice:

  • Private Sub ListBox1_Click()
    miodato = ListBox1.Value      'con "miodato" prendiamo il valore selezionato nella listbox
    Sheets(2).[G7] = miodato  
    'e lo copiamo nella cella G7 del foglio2
    End Sub

Potremo anche predisporre istruzioni che rendano variabile sia il foglio di destinazione, sia la colonna, sia la riga da cui iniziare a incollare. Potremo optare per InputBox con le quali richiedere i dati necessari, oppure predisporre delle TextBox nella UserForm nelle quali scrivere di volta in volta il numero o il nome del foglio destinazione, il numero di colonna, o/e il numero di riga. Una opportunità per tutti coloro che disponendo di un foglio di registrazione dati generale, vogliano per esempio, imputare valori a colonne di fogli diversi, ma il cui nome figura in uno dei campi del database generale. Oppure eseguire copie di dati multipli, caricando la ListBox su più colonne.

Faremo quindi un'esempio un pò più articolato, ma più veritiero, su una situazione del genere:

  • un foglio database dove inserire dati quotidianamente: simuliamo un progetto di una gestione spedizioni a vari depositi dove useremo 4 campi: un campo per la Data di spedizione, uno per la Città deposito di destinazione, uno per il numero del documento di vettura, uno per l'importo delle spese trasporto. Chiaramente sono campi modificabili e ampliabili, ma serve a presentare l'esempio.

Cosa vorremo ottenere?

  • vorremo selezionare tutta l'area dati corrispondente alle spedizioni effettuate in uno stesso giorno, e imputare ai relativi fogli che avremo rinominato ognuno col nome della città di ogni deposito, i dati relativi (giornalieri) relativi a tre campi: la data, il numero documento, l'importo, in modo da creare per ogni deposito, una tabella progressiva di questi dati, ordinata per data.

L'esercizio è stato impostato su questo esempio e queste necessità, ma ovviamente è applicabile a situazioni similari dove vorremo creare selezioni variabili giorno dopo giorno. Vediamo un immagine della tabella usata come campione:

Come anticipato, disponiamo di quattro campi ; per poter agire, avremo bisogno di visualizzare nella ListBox della form che apriremo per lavorare, tutti e quattro i campi per ogni riga, dovremo infatti identificare la città del campo "Deposito" perchè i dati al nome correlati (stessa riga nella ListBox) vengano copiati nel foglio che porterà lo stesso nome. Per ottenere una visione a quattro colonne nella ListBox, dovremo agire sulla sua proprietà "ColumnCount" impostando il valore "4". Poi, con le istruzioni, sfrutteremo la proprietà "List" della ListBox per determinare il valore di quale colonna, della riga che sarà selezionata, assegnare alla cella, stesso campo, del foglio destinazione.

Useremo un pulsante che chiamerà la UserForm, e come spiegato negli esempi precedenti, sfrutteremo il RefEdit per reperire l'intervallo selezionato e caricare così la ListBox. Vediamo un immagine di come apparirà la situazione:

Come si vede, l'area selezionata sul foglio (bordata di rosso) corrisponde a tutti i movimenti relativi alla stessa data (criterio di selezione), e con la routine già vista sopra, carichiamo con l'evento Change del Refedit, il RowSource della ListBox1, che con ColumnCount impostato a 4, ci consente di visualizzare tutte le quattro colonne selezionate sul foglio. In questo modo selezionando il nome del deposito nella ListBox, copieremo nel foglio con lo stesso nome, i dati della prima, terza e quarta colonna della ListBox (stessa riga). La seconda colonna è inutile copiarla, visto che ci serve solo a identificare il nome del deposito e quindi del foglio che porterà lo stesso nome, anzi la usiamo con una itruzione che copierà nella textbox1 il nome della città; abbiamo infatti aggiunto tre textbox che servono a rendere variabili sia il nome del foglio nel quale andare a copiare (textbox1), sia da quale riga iniziare a copiare (textbox2), sia da quale colonna iniziare a copiare (textbox3).

L'aggiunta di tre textbox l'ho fatta solo a titolo dimostrativo, per far vedere come reperire tre variabili da assegnare aleìle istruzioni che vedremo, in realtà in questo esempio dove intendiamo accodare i dati a dati già esistenti, non era necessario rendere variabile il numero riga e il numero di colonna, ma inserirli come costanti direttamente nel codice, e il nome del foglio l'avremmo preso di volta in volta dal nome del deposito/foglio presente nella listbox, che avremmo selezionato. L'azione di copia avviene "a distanza", cioè non facciamo selezionare il foglio in cui copieremo; questo comporta che con un semplice click su ogni riga nella listbox, poichè attiviamo il suo evento Click, eseguirà le istruzioni senza che a video si notino cambiamenti; per questo ho inserito a fine istruzione il comando per un messaggio che avvisi l'esecuzione della copia.

Ho inserito anche una verifica che controlla se esiste un foglio col nome deposito che verrà selezionato, nel caso non esista, si viene invitati a chiudere la form e controllarecome il nome è scritto nelle celle oppure a creare un nuovo foglio con quel nome. Non ho invece inserito un controllo che verifichi se la zona copiata è composta da 4 colonne, ma  ce ne accorgeremmo subito guardando la ListBox e vedendo che mancano dati; sarà sufficiente agire di nuovo sul RefEdit.

Vediamo le istruzioni:

la prima, già vista, serve per assegnare al RowSource della ListBox l'intervallo reperito con RefEdit:

  • Private Sub RefEdit1_Change()
    ListBox1.RowSource = RefEdit1.Text
    End Sub

La seconda, inserita nell'evento Click della ListBox, si occupa di tutto il resto (in verde i commenti):

Private Sub ListBox1_Click()

'sotto: la variabile "n" serve per reperire il numero di riga (indice di riga) della riga che abbiamo in 'quel momento selezionato
n = ListBox1.ListIndex

'sotto: per evitare di scrivere noi il nome del foglio nella textbox1, nome necessario poi ad 'identificare su quale foglio andranno copiati i dati,  rendiamo textbox1 uguale al valore che è  'nella lista, riga "n" (quella selezionata), seconda colonna. Ricordo che gli indici colonna in una 'ListBox.List iniziano da sinistra con la colonna 0, quindi la seconda è la numero 1
TextBox1.Text = ListBox1.List(n, 1)
'sotto: usiamo tre variabili, più comode da scrivere nei riferimenti , per assegnargli i rispettivi valori 'che saranno nelle textbox. NOTA: per le textbox2 e 3 che ospiteranno il numero riga e colonna, 'dobbiamo assicurarci di definire di che "tipo di dati" si tratta, cioè valori numerici, quindi si usa 'Val(TextBoxX)
x = TextBox1.Text
'nome foglio
y = Val(TextBox2.Value)
'n. riga
z = Val(TextBox3.Value)
'n.colonna

'sotto: un semplce controllo che le tre textbox contangano i dati necessari, per evitare poi errori di 'debug per l'omissione dei valori di identificazione indici. Ovviamente queste istruzioni si potevano 'omettere se nell'assegnazione delle variabili sopra, avessimo imputato valori costanti, come ad 'esempio y = 1 e z = 1 
If TextBox1 = "" Or TextBox2 = "" Or TextBox3 = "" Then
MsgBox "INSERIRE TUTTI I DATI"
Exit Sub
 'si esce dalla routine se uno dei dati manca
End If
'sotto: ora inizia il ciclo che cercherà la prima riga libera sul foglio destinazione
Dim iRow As Integer
iRow = y
 'si indica con iRow che sarà uguale al valore assegnato a "y", la riga di inizio incollaggio


On Error Resume Next
'attiviamo la gestione degli errori nel caso che il nome del foglio 'rappresentato da "x" non sia trovato

'sotto: iniziamo un ciclo While che cercherà la prima cella libera, riga iRow, colonna "z", sul foglio '"x"; è a questo punto, se non esiste un foglio di nome uguale a "x" che si genere l'errore di run-'time 9: in questo caso si avvisa con un messaggio e si esce dalla routine
While Sheets(x).Cells(iRow, z) <> ""
If Err.Number = 9 Then
MsgBox "Foglio Non Presente - Chiudere la Form e" & vbLf _
& "controllare esattezza del nome. Oppure" & vbLf _
& "Creare un nuovo foglio"
Exit Sub
End If

'se invece il foglio (Sheets(x)) è presente, si incrementano le righe di 1 fino a che non si verifica la 'condizione richiesta da While, e si esce con Wend
iRow = iRow + 1
Wend
'sotto: assegnamo a tre variabili (d,g,h) tre valori nella riga selezionata. Va precisato che la 'proprietà Text della ListBox identifica di default il valore che si trova nella riga selezionata, e nel 'caso di una ListBox a più colonne, sempre il valore nella colonna 0, la prima a sinistra (che 'nell'esempio corrisponde ad una data. Poi prendiamo i valori nella terza (2) e quarta (3) colonna
d = ListBox1.Text
g = ListBox1.List(n, 2)
h = ListBox1.List(n, 3)
'sotto: a questo punto, sul foglio "x" nella riga iRow (quella trovata lbera), colonna "z", copiamo il 'valore rappresentato da "d", stessa riga, ma nella colonna a destra rispetto alla colonna "z" (z + '1) il valore rappresentato da "g", e stessa riga, ma due colonne a destra rispetto alla colonna "z" 
'(z + 2) il valore rappresentato da "h".

Sheets(x).Cells(iRow, z) = d
Sheets(x).Cells(iRow, z + 1) = g
Sheets(x).Cells(iRow, z + 2) = h
MsgBox "Copia Eseguita" 
' si avvisa della copia effettuata
End Sub

un accorgimento: nella sezione Generale - Dichiarazioni della userform, metteremo questa istruzione che servirà a non creare equivoci se un nome di deposito e lo stesso di un foglio, fossero scritti con diversità maiuscole/minuscole:

  • Option Compare Text

Mi sembra un buon esercizio, utile ed ampliabile, e le spiegazioni vi dovrebbero mettere in grado di modificarlo a volontà. Leggete anche gli altri articoli che parlano di ListBox, per familiarizzarvi con questi "oggetti ActiveX".

Agggiungo una variante alla routine appena vista, per dimostrare l'ampliabilità delle istruzioni : inseriamo le istruzioni che se un foglio col nome uguale al deposito selezionato non esiste, ci venga posta la domanda se desideriamo aggiungerlo; in caso positivo, faremo aggiungere il nuovo foglio alla fine dei fogli già presenti, ne imposteremo la colonna A (quella che dovrà contenere le date) come "tipo di dato" (con NumberFormat) a formato Data, e ci copieremo i dati relativi alla voce selezionata, tutto in sequenza; dovremo aggiungere istruzioni per ritornare al foglio di origine, visto che il metodo Add porta il focus sul nuovo foglio. Se invece alla domanda risponderemo "No", si uscirà dalla routine. Vediamo le modifiche, che evidenzio in grassetto:

Private Sub ListBox1_Click()
Application.ScreenUpdating = False
Dim Origine
Set Origine = Sheets("Foglio1") 
'foglio col database di origine dati
n = ListBox1.ListIndex
TextBox1.Text = ListBox1.List(n, 1)

x = TextBox1.Text 'nome foglio
y = Val(TextBox2.Value) 'n. riga
z = Val(TextBox3.Value) 'n.colonna
If TextBox1 = "" Or TextBox2 = "" Or TextBox3 = "" Then
MsgBox "INSERIRE TUTTI I DATI"
Exit Sub
End If

Dim iRow As Integer
iRow = y
On Error Resume Next
While Sheets(x).Cells(iRow, z) <> ""
If Err.Number = 9 Then
idomanda = MsgBox("Foglio non presente - Vuoi aggiungere il Foglio ?", vbYesNo)
If idomanda = vbYes Then
Sheets.Add.Name = x   
'si aggiunge un foglio a nome "x"
Sheets(x).Move After:=Sheets(Sheets.Count) 
'si sposta alla fine dei fogli presenti
Sheets(x).Range("A:A").NumberFormat = "dd/mm/yy"
'si imposta il formato celle in A
GoTo 10:
Else

Exit Sub
End If
End If
iRow = iRow + 1
10:
Wend

d = ListBox1.Text
g = ListBox1.List(n, 2)
h = ListBox1.List(n, 3)

Sheets(x).Cells(iRow, z) = d
Sheets(x).Cells(iRow, z + 1) = g
Sheets(x).Cells(iRow, z + 2) = h
Origine.Activate
MsgBox "Copia Eseguita"
End Sub

 

Buon lavoro.

prelevato sul sito www.ennius.altervista.org