In questa lezione vedremo come inserire un articolo tramite VBA direttamente nel foglio Preventivi, inizieremo dalla struttura del foglio “Listino” che sarà quello in cui archivieremo i dati degli articoli.
Io ho pensato di dividere gli articoli in categorie merceologiche, per una facile e veloce consultazione.
Vediamo come ho strutturato il foglio



Come vedete ho usato le prime 15 colonne per creare la prima categoria, separando la seconda categoria (e le altre 15 colonna) con una colonna vuota (quella nera), la riga 1 ospita il titolo della categoria (rif. A1), la riga 2 le etichette (rif. A2) per le varie colonne e dalla riga 3 inseriamo tutti gli articoli con i relativi dati. Come abbiamo appena citato, ogni categoria è composta da 15 colonne, divise da una colonna, considerando che un foglio di excel ha 256 colonne disponibili, facendo un rapido calcolo, dividendo le 256 colonne totali per le 16 utilizzate (15 + la colonna vuota) danno come risultato 16 categorie, che a mio avviso è un numero più che soddisfacente di categorie.

Detto questo ora passiamo al nostro pulsante “Inserisci Articoli” e vediamo come usare una userform per andare a leggere i dati nel foglio “Listino” e inserirli nel foglio principale “Preventivi”. Cliccando sul pulsante ci appare una Userform come questa



Per popolare il primo Listbox con i nomi delle varie categorie abbiamo inserito nell’evento UserForm_Activate questo codice :

Private Sub UserForm_Activate()
If [P14] = "" Then MsgBox "Devi creare un nuovo documento", vbExclamation: End i = 1
Do Until Sheets("Listino").Cells(1, i) = Empty
FormArt.ListBox1.AddItem (Sheets("Listino").Cells(1, i).Value)
i = i + 16
Loop
End Sub

Commentiamo il codice :

If [P14] = "" Then MsgBox "Devi creare un nuovo documento", vbExclamation: End

Questa istruzione genera una condizione [If] che il programma dovrà verificare, ricordiamo che abbiamo il foglio “Preventivi” attivo, per cui il nostro codice possiamo interpretarlo così :
if [se] nella cella P14 (n° documento) (è scritta in valore assoluto, che corrisponde a Range (“P14”) ) è uguale a stringa vuota [“”] allora [Then] stampa a video [MsgBox] questo messaggio [Devi creare un nuovo documento] e se ricordate le opzioni della funzione MsgBox con vbExclamation inseriamo nella form l’icona di esclamazione, con l’istruzione End terminiamo la procedura in quanto non è stata soddisfatta la condizione.

Il box appare così



Il seguito del listato riporta questo codice

i = 1
Do Until Sheets("Listino").Cells(1, i) = Empty
FormArt.ListBox1.AddItem (Sheets("Listino").Cells(1, i).Value)
i = i + 16
Loop

Questo è il codice che riempirà il Listbox1 con i nomi delle categorie, vediamolo assieme
Nella prima riga dichiariamo una variabile [i] uguale a 1 e poi tramite un ciclo Do Loop scorriamo tutto il foglio “Listino” nell’intervallo [.Cells(1,i)], cioè dalla riga 1 colonna i, (che i al primo ciclo vale 1) per cui iniziamo dalla riga 1 colonna 1, notare che la variabile è stata inserita nell’enunciato Cells come riferimento di colonna, per cui nel nostro ciclo la riga rimarrà fissa mentre varieranno i riferimenti di colonna, in pratica scorriamo tutte le colonne della riga 1.
Ora mentre scorriamo il nostro foglio inseriamo anche i valori che troveremo nel foglio “Listino” nel Listbox1 con l’enunciato

FormArt.ListBox1.AddItem (Sheets("Listino").Cells(1, i).Value)

Con il comando FormArt.ListBox1.AddItem aggiungiamo le varie voci al Listbox, dove FormArt è il nome che abbiamo dato alla nostra Userform come si vede in figura



e con il comando Additem aggiungiamo le voci trovate nell’intervallo specificato del foglio “Listino”, mentre con (Sheets("Listino").Cells(1, i).Value), scorriamo tutto il foglio (Listino) alla riga 1 colonna i, come abbiamo poco sopra spiegato.

Sappiamo che il nostro foglio è diviso in colonne che costituiscono le varie categorie, ogni categoria è composta da 15 colonne ed è divisa dalla prossima categoria da una colonna vuota (quella nera), in pratica dobbiamo scrivere il nome della categoria nella prima colonna, e se contiamo le colonne che ci sono tra una categoria e l’altra sono 16 per cui con il comando

i = i + 16

incrementiamo il valore della variabile i di 16 unità, e dato che i vale 1 la seconda categoria la troviamo alla colonna [i+16] cioè alla 17° colonna, come la 2° categoria la troviamo alla colonna [i+16] cioè [17+16]=33, perché al secondo giro del ciclo la variabile i assume il valore 17.
Tutto questo finche non troviamo una colonna vuota [Empty] e il ciclo si ferma. Ora abbiamo popolato il primo Listbox con i nomi delle categorie, e se clicchiamo su una voce ci vengono riportati tutti gli articoli in essa inseriti, come da immagine



Per polare anche il 2° box abbiamo usato questo codice inserito nell’evento ListBox1_Clik

Private Sub ListBox1_Click()
posiz = (ListBox1.ListIndex + 1) * 16 - 15
If ListBox2.ListCount >= 1 Then ListBox2.Clear
i = 3
Do Until Sheets("Listino").Cells(i, posiz) = Empty
FormArt.ListBox2.AddItem (Sheets("Listino").Cells(i, posiz + 1).Value)
i = i + 1
Loop
cancella
End Sub

Vediamo la prima riga di codice posiz = (ListBox1.ListIndex + 1) * 16 - 15. Questo enunciato è molto importante e ci serve per calcolare la colonna in cui dobbiamo andare a cercare i nostri articoli, è una procedura che abbiamo già fatto nel caricamento della Userform con l’evento Activate, ma in quel caso passavamo tutta la prima riga fino alla fine, con un salto ciclico, e non potevamo usare la stessa variabile (i) in quanto la stessa cambiava continuamente di valore fino alla fine del ciclo, per cui dobbiamo sfruttare l’evento Clik per sapere quando clicchiamo su una voce dove dobbiamo andare a cercare.
Vediamo come lavora questo sistema, cominciando dalla funzione ListBox1.ListIndex, e senza tanti tecnicismi possiamo capirne il funzionamento modificando il listato facendolo diventare così

Private Sub ListBox1_Click()
posiz = (ListBox1.ListIndex + 1)
MsgBox posiz
End Sub

Se facciamo “girare” questa routine ci appare un box come quello raffigurato qui sotto



Ora è facile intuire che ciccando su una voce del Listbox la funzione ci riporta la posizione numerica della voce in elenco, in sostanza se clicchiamo sulla prima voce ci riporta il valore 1, sulla seconda il valore 2 e così di seguito, ritorniamo al listato originale

Private Sub ListBox1_Click()
posiz = (ListBox1.ListIndex + 1) * 16 - 15
If ListBox2.ListCount >= 1 Then ListBox2.Clear
i = 3
Do Until Sheets("Listino").Cells(i, posiz) = Empty
FormArt.ListBox2.AddItem (Sheets("Listino").Cells(i, posiz + 1).Value)
i = i + 1
Loop
cancella
End Sub

Risulta che la variabile posiz ciccando sulle voci del Listbox assume il valore calcolato in questo modo

Voce 1 = 1*16-15 = 1
Voce 2 = 2*16-15 = 32-15 = 17
Voce 3 = 3*16-15 = 48-15 = 33

Perché i valori 16 e 15? Perché abbiamo creato un foglio che raccoglie le categorie dei nostri articoli con un intervallo di 16 colonne (compresa quella vuota) se avessimo fatto un intervallo di 10 colonne avremmo dovuto moltiplicare per 10 e sottrarre 9.
In definitiva cosa sono questi valori (1-17-33)? Sono i numeri di colonna in cui iniziano le nostre categorie e nelle quali dobbiamo scorrere le righe per trovare gli articoli inseriti. Continuiamo col codice

If ListBox2.ListCount >= 1 Then ListBox2.Clear

Con ListBox2.ListCount facciamo una “conta” delle voci presenti nel Listbox2 e nel nostro caso ho voluto verificare che il Listbox2 sia vuoto, per cui il resto del codice possiamo interpretarlo così:

se [If] il numero di voci nel Listbox2 [ListBox2.ListCount] sono maggiori o uguali [>=] a 1 allora [Then] le cancelliamo.
Subito dopo dichiariamo una variabile e le diamo un valore

i = 3

Questa rappresenta la riga di inizio in cui cercare gli articoli, dichiarandola con valore = 3 decidiamo di partire dalla terza riga, perché abbiamo visto all’inizio che la prima riga è destinata al nome della categoria, la seconda alle etichette delle colonne e dalla terza inseriamo i nostri articoli.
Il seguito del codice svolge la ricerca degli articoli e li aggiunge nel Listbox

Do Until Sheets("Listino").Cells(i, posiz) = Empty
FormArt.ListBox2.AddItem (Sheets("Listino").Cells(i, posiz + 1).Value)
i = i + 1
Loop

In sostanza ripetiamo l’operazione che abbiamo visto prima per il Listbox1, con la sola differenza che incrementiamo le righe invece delle colonne [.Cells(i, posiz)] finche non troviamo una riga vuota [= Empty] e carichiamo le voci trovate nel nostro Listbox, ora carichiamo il Listbox2 con gli articoli trovati tramite l’enunciato

FormArt.ListBox2.AddItem (Sheets("Listino").Cells(i, posiz + 1).Value)

Questo codice è molto simile a quello che abbiamo usato per il ListBox1, con la differenza che scorriamo le righe invece delle colonne, ma anche per il valore che usiamo della variabile posiz

Cells(i, posiz + 1).Value

Come vedete abbiamo usato un +1, cioè carichiamo la colonna sucessiva a quella processata nella ricerca, perchè abbiamo preferito caricare nel Listbox2 la descrizione dell’articolo, che si trova nella colonna a fianco di quella che stiamo scorrendo.
Se avessimo voluto caricare la colonna processata avremmo tolto il valore +1 e avremmo popolato il Listbox2 con il codice dell’articolo invece della descrizione

Poi incrementiamo il contatore di una unità per passare alla riga successiva

i = i + 1

e il ciclo si ferma quando trova una riga vuota. Ora abbiamo caricato tutti e 2 i Listbox nella nostra Userform, dobbiamo solo cliccare sul pulsante per inserire tutti i dati relativi all’articolo nel foglio “Preventivi” ma questo argomento lo vedremmo nella prossima lezione, che sarà presto online