ComboBox (o ListBox) e i Fogli di lavoro. - dal 04/09/04 pagina vista: volte

Nasce da una richiesta questo semplice esercizio:

Caricare nel RowSource di una ComboBox o di una ListBox, l'elenco di tutti i fogli di lavoro presenti nella cartella senza preoccuparci di come avremo rinominato i fogli. Verranno infatti riportati gli esatti nomi così come verranno letti.

Necessità: disporre su una UserForm di un elenco dei Fogli di lavoro per selezionare il foglio sul quale andranno copiati i dati che saranno nelle TextBox presenti sulla UserForm.

In genere predisponiamo, rinominandoli, più fogli di lavoro quando vogliamo differenziare registrazioni basate su nominativi, oppure su nomi di mesi, o ancora su gruppi di lavoro, o ancora su numeri progressivi, oppure per nome di depositi, o ancora su....ecc.ecc., sono talmente varie le necessità che mi risulta impossibile elencarle tutte.

In pratica quando vogliamo scegliere liberamente un foglio dove andare a copiare i dati che inseriremo nelle TextBox. Impostiamo quindi un esempio basato sulla necessità di registrare dati di fatturazione clienti su Fogli chiamati col nome degli  Agenti. Lo scopo sarà quello di gestire un progressivo Agente per la determinazione del Fatturato Agente e relativi conteggi provvigionali. L'esempio lo limito ovviamente solo alle istruzioni per le operazioni di caricare il RowSource della ComboBox e per la copia dati sul foglio destinazione.

Per reperire i nomi dei fogli, useremo l'apertura della UserForm, sfruttando il suo evento Activate, e inserendo un ciclo For Each ...Next che legga i nomi di tutti i fogli della cartella, così:

  • Private Sub UserForm_Activate()
    For Each ws In Worksheets
    ComboBox1.AddItem ws.Name
    Next
    End Sub

L'istruzione è semplice da capire: per ogni (For Each) foglio (ws) nell'insieme Fogli (Worksheets), aggiungi (AddItem) in nome del Foglio (ws.Name) in quel momento letto (con Next si passa al successivo, fino ad averli letti tutti) nella ComboBox1. Se invece di una ComboBox avremo optato per una ListBox, l'istruzione sarà la stessa e dovremo solo cambiare ComboBox1 con ListBox1 (o il numero indice che sarà). E questo sarà il risultato dell'istruzione sopra:

Chiameremo l'UserForm dal Foglio Base, e come si vede, la ComboBox1 riporterà i nomi dei Fogli.

Inseriremo sulla UserForm tante TextBox quanti saranno i campi da registrare, ed un CommandButton per l'attivazione delle istruzioni di copia. Chiaramente i dati (campi) da inserire saranno gli stessi per tutti gli Agenti, e manterremo la stessa sequenza tabellare (ordine dei campi) su ogni foglio di lavoro. Supponiamo quindi che vorremo registrare questi dati.

  • Data fattura

  • Numero di fattura

  • Nominativo cliente

  • Importo fattura netto di iva (l'importo dovrà servire per il calcolo provvigioni)

Ognuno deciderà le informazioni da registrare, io ne ho messe solo 4 come esempio. E questa sarà la nostra maschera di introduzione dati:

I Fogli di lavoro porteranno a loro volta, a partire da una colonna, la A per esempio, e da una riga, la 1 per esempio (ma potremo fare iniziare le tabelle dalla colonna e dalla riga che ci pare) le stesse intestazioni di campo, e nelle stesse posizioni per ogni foglio.

Per l'attivazione delle istruzioni potevamo scegliere anche l'evento Change (o Click) della ComboBox, io ho optato per il pulsante. Buona norma sarà prevedere dei controlli che verifichino che le TextBox non siano vuote (vedi anche articolo "TextBox e inserimento dati") e che nella ComboBox non sia selezionato nulla o il "Foglio Base", per cui andrà usata un'istruzione come

  • Private Sub CommandButton1_Click()
    If ComboBox1.Text = "Foglio Base" Or ComboBox1.Text = "" Then
    MsgBox "Seleziona il Foglio destinazione"
    Exit Sub
    End If
    .....seguono istruzioni

che ci farà uscire dalla routine se non avremo selezionato un Foglio "agente" o sarà vuota.

Avremo bisogno di una variabile a cui assegnare il nome del Foglio che selezioneremo nella ComboBox (una X andrà bene)

  • X = ComboBox1.Text

e avremo anche bisogno di un ciclo While ..Wend che cerchi la prima riga libera nella colonna a sinistra della tabella sul foglio scelto, visto che vorremo accodare successive registrazioni e non sostituire quella esistente, quindi la nostra routine sarà questa:

  • Private Sub CommandButton1_Click()
    If ComboBox1.Text = "Foglio Base" Or ComboBox1.Text = "" Then
    MsgBox "Seleziona il Foglio destinazione"
    Exit Sub
    End If
    'sotto: controlliamo con un ciclo For Next che le 4 textbox non siano vuote, altrimenti usciamo avvisando
    For I = 1 To 4
    If UserForm1.Controls("TextBox" & I).Text = "" Then
    MsgBox "MANCANO DATI"
    Exit Sub
    End If
    Next

    X = ComboBox1.Text       'prendiamo con X il nome del foglio selezionato nella ComboBox
    riga = 1        'impostiamo la riga iniziale sul foglio X in cui cercare la prima riga libera(nella colonna A (la 1))
    While Sheets(X).Cells(riga, 1) <> ""     'e iniziamo il ciclo di ricerca della prima riga libera, colonna 1 (la A)
    riga = riga + 1
    Wend
    'trovata la riga libera, copiamo il contenuto delle textbox nelle celle, stessa riga, colonne a seguire (A,B,C,D). 'Importante in questa fase definire il "tipo di dati" che si stanno passando, usando la "Funzione di Conversione" 'specifica per ogni "tipo di dati".
    Sheets(X).Cells(riga, 1) = CDate(TextBox1)       'la data
    Sheets(X).Cells(riga, 2) = Val(TextBox2)       'il numero fattura
    Sheets(X).Cells(riga, 3) = TextBox3      'il cliente
    Sheets(X).Cells(riga, 4) = CDbl(TextBox4)      'l'importo con decimali
    MsgBox "Registrazione Eseguita"       'avvisiamo della avvenuta registrazione

    For I = 1 To 4      'puliamo le textbox per un nuovo inserimento
    UserForm1.Controls("TextBox" & I).Text = ""
    Next
    ComboBox1.Text = ""     'e anche la Combobox1
    End Sub

 

 

prelevato sul sito www.ennius.altervista.org