Registrare Prenotazioni su un Foglio di lavoro.  (04/05/03)

Uno fra i lavori che vorremmo realizzare con Excel, vista la sua naturale impostazione in righe e colonne che formano tante celle, è certamente quello di crearci un "Planning", cioè un foglio dove poter registrare prenotazioni di Camere d'albergo, o anche di appartamenti nei Bed and Breakfast, ma potrebbe essere anche un Planning per turni di lavoro, per appuntamenti, per.... per.... Tanti sono i motivi. Normalmente ci si affida ad un planning Mensile, e facendo l'esempio sulle camere, per ogni camera, ma potrebbe essere per ogni dipendente nel caso dei turni di lavoro, ecc.ecc.

Presento un esempio impostato sulla necessità di controllare visivamente i giorni di prenotazione di una camera, in un Planning annuale. Come controllo, coloreremo le celle corrispondenti alle prenotazioni, di un colore in contrasto col  resto del foglio. Vediamo l'esempio del Planning:

Come vedete, è stata predisposta una griglia, che per quanto riguarda le celle interessate ai giorni e ai mesi, partono dalla colonna C (quindi la numero 3), e dalla riga 5. Questi riferimenti sono importanti perchè ci serviranno nelle istruzioni, da quale riga e quale colonna iniziare a contare.

Prima di passare ad illustrare le procedure adoperate, premetto un'altra necessità che sorge nel determinare, quanti giorni possiede ogni mese, visto che non tutti i mesi sono di 31 giorni. Per questo ho realizzato una Funzione Utente, che verrà poi richiamata nelle routine. Questa la funzione, che restituisce il numero di giorni in funzione del mese espresso come numero (1=gen, 2=feb, ecc):

nella sezione Generale  -  Dichiarazioni  di un Modulo, inseriamo questa istruzione, per "aiutare" la funzione ad essere visibile anche al di fuori del modulo stesso:

Option Explicit

___________________________________________________________________

Function giorni(mese)
'questa funzione serve a definire quanti giorni ha un mese in modo che nel calcolo di 'registrazione sul foglio si possa determinare automaticamente il fine mese. Per
'febbraio ho considerato fisso 28 giorni. Nel bisestile lo farete manualmente di colorare il  'giorno 29.
Select Case mese
Case 1, 3, 5, 7, 8, 10, 12
giorni = 31
Case 4, 6, 9, 11
giorni = 30
Case 2
giorni = 28
End Select
End Function

Ho poi usato una UserForm che richiamo col CommandButton sul foglio, e nella quale inseriremo le due date: di inizio e di fine prenotazione. Le date le inseriremo in due TextBox, e per comodità visiva ho usato altre 4 textbox nelle quali svolgeremo delle trasformazioni. Premesso che per queste operazioni si può usare delle variabili anzichè delle textbox, io ho usato queste perchè più facilmente rintracciabili nelle istruzioni e rendono, credo, più comprensibile il tutto. Sulla UserForm ho inserito due altri pulsanti: quello per lo sviluppo delle routine che coloreranno le celle, ed uno per ripristinare il colore di fondo, "ripulendo" quindi la tabella.

Vediamo la UserForm:

e questo sarà il risultato:

Per le Textbox indicate dalle frecce, imposteremo poi la loro proprietà "Visible" a False. Nelle due textbox indicate in blu, estraggo il giorno presente nella textbox a sinistra, e nelle due indicate in rosso, estraggo il mese (in numero). Le routine sono state inserite nell'evento Exit della textbox (la 2) relativa alla data di partenza, in modo che, spostandoci col Focus sulla textbox (la 7) della camera N°, si attivi l'evento. Queste le istruzioni:

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox3 = Day(CDate(TextBox1)) 'restituisce il giorno della data di arrivo
TextBox5 = Month(TextBox1) 'e il mese espresso in numero

TextBox4 = Day(CDate(TextBox2)) 'restituisce il giorno della data di partenza
TextBox6 = Month(TextBox2) 'e il mese espresso in numero
End Sub

Come già detto, è possibile usare delle varianti anzichè delle textbox, esempio:

giornoarrivo = Day(CDate(TextBox1)) 'restituisce il giorno della data di arrivo
mesearrivo = Month(TextBox1) 'e il mese espresso in numero

basterà poi usare i nomi delle varianti (giornoarrivo, mesearrivo) nelle istruzioni al posto del riferimento alle textbox.

Sempre nella Userform vediamo la TextBox7 dove appare il numero della camera. Questo numero dovrà corrispondere anche al nome assegnato al foglio di lavoro che identificherà appunto il planning relativo a quella camera, inoltre il numero camera ci servirà anche per controllare se siamo sul foglio giusto quando premeremo il pulsante "Pulisci Foglio Attivo". Infatti nell'evento Click del commandbutton facciamo controllare se il nome del foglio corrisponde al numero camera; in caso positivo si ripristina il colore di tutte le celle (in questo caso in grigio), altrimenti veniamo avvisati con un messaggio. Questa la routine:

Private Sub CommandButton2_Click()
'queste istruz. controllano se siamo sul foglio che corrisponde al numero scritto nella 'textbox7, e in caso positivo colorano tutte le celle dei giorni in grigio
If ActiveSheet.Name = TextBox7.Value Then
With Range("C5:AG16")
.Cells.Interior.ColorIndex = 15
End With
Else
MsgBox "Non siamo sul Foglio giusto"
End If
End Sub

Anzichè usare una textbox nella quale digitare il numero ( in lettere ) potremmo usare una ComboBox che "peschi" con il suo RowSource un elenco con i numeri delle camere, e basterà poi selezionare il numero voluto nella ComboBox. In questo caso va modificata l'istruzione di confronto in questo modo:

If ActiveSheet.Name = ComboBox1.Text Then

Passiamo ora ad esaminare le istruzioni collegate all'evento Click del pulsante "Registra sul foglio".

Private Sub CommandButton3_Click()
'sotto: leggo il nome del foglio che corrisponde alla textbox7 e lo seleziono
Worksheets("" & TextBox7.Value & "").Select

'sotto: inizia il controllo delle date per colorare le celle. Si possono verificare 3 condizioni:

'1) le due date corrispondono allo stesso mese es: 10/03/03 - 25/03/03

'2) le due date accavallano un mese  es:  20/03/03 - 05/04/03

'3) le due date accavallano più di un mese es:  20/04/03 - 05/07/03

'Per stabilire le tre condizioni sopra citate, usiamo un cico If... Then..ElseIf; nella prima 'condizione verifichiamo se i valori (numeri) che rappresentano i mesi, e che si trovano 'nelle textbox 5 e 6 sono uguali (cioè se le date appartengono allo stesso mese), se la 'condizione è vera, allora eseguiamo le istruzioni:
If TextBox5.Value = TextBox6.Value Then

'assegniamo alla variabile r il valore che è nella textbox5 e che serve a conoscere quale 'riga identificare, cioè nell'esempio la riga 4 (aprile) AGGIUNGENDO il numero di 'quante righe ci sono prima che inizi la tabella, che sono 4 (gennaio inizia dalla riga 5), 'quindi 4+ 4 = 8 (infatti nella tabella il mese Aprile si trova sulla riga 8)
r = TextBox5.Value + 4 'seleziona la riga del mese

'ora assegniamo alla variabile c il valore che si trova nella textbox3 (cioè 15, che è il 'giorno) e che serve a identificare la colonna del giorno di inizio. Poichè anche in questo 'caso come per le righe,  le colonne dei giorni iniziano dalla C cioè la colonna 3, 'aggiungiamo 2 al valore della textbox3 che sarà 15+2=17 (infatti nella tabella il giorno 15 'è nella colonna Q che è la diciassettesima da sinistra)
c = Val(TextBox3) + 2
f = Val(TextBox4) + 2

'a questo punto sappiamo con r in quale riga assegnare il colore, e con c ed f abbiamo i 'giorni, e quindi usiamo queste variabili per identificare il range di celle da colorare.
Range(Cells(r, c), Cells(r, f)).Interior.ColorIndex = 40

'sotto abbiamo la seconda condizione, cioè se le date accavallano un mese, esempio dal 15/04/03 al 05/05/03 (che è l'esempio citato nell'immagine della userform); quindi 'controlleremo se la textbox6 sarà uguale al valore della textbox5 +1, in caso positivo, 'seguono le istruzioni che assomigliano a quelle sopra, solo che dovremo identificare due 'righe (quelle dei due mesi che appartengono alle due date), in più abbiamo bisogno di 'sapere quando finisce il mese appartenente alla data di ingresso, e per fare questo usiamo 'la funzione vista inizio pagina, che dato il numero del mese, restituisce di quanti giorni è 'formato, e ci servirà per determinare quale colonna usare per colorare il periodo dalla 'data di inizio, alla fine del mese. Mentre per il mese successivo sapremo che inizia dalla 'colonna 3, mentre i giorni li prenderemo dalla Data della partenza.
ElseIf TextBox6.Value = Val(TextBox5) + 1 Then
r = TextBox5.Value + 4 'identifica la riga del mese
c = Val(TextBox3) + 2  'identifica colonna del giorno di arrivo
f = giorni(TextBox5) + 2 ' identifica tramite la funzione "giorni" la fine del mese e quindi, 'riga sotto, colora le celle del range cosi reperito (data arrivo-fine mese)
Range(Cells(r, c), Cells(r, f)).Interior.ColorIndex = 40

'sotto:ora si reperiscono riga (cioè mese), colonna iniziale(la 3) e giorno della Data di 'partenza e si colorano le celle
rr = TextBox6.Value + 4
cc = 3
ff = TextBox4.Value + 2
Range(Cells(rr, cc), Cells(rr, ff)).Interior.ColorIndex = 40
 

'terza condizione: questa condizione deve prevedere un numero imprecisato di mesi che 'possano intercorrere tra le due date; infatti possiamo avere due date 02/04/03-05/06/03 '(tre mesi: aprile,maggio,giugno), ma potrebbero essere 10/04/03-15/08/03 (cinque mesi: 'aprile,maggio,giugno,luglio,agosto). Abbiamo bisogno di inserire un ciclo che valuti quanti 'mesi sono presenti fr le due date, e ci affideremo ad un ciclo For..Next.

'quindi controlleremo se la data di partenza è maggiore rispetto alla data di arrivo + 1 'mese , in caso positivo il codice eseguirà queste istruzioni, che differiscono dalle 'precedenti solo per quanto riguarda il ciclo For Next che assegnerà a mesi interi la 'colorazione per tutto il mese. In questo caso i giorni saranno reperiti dall'inizio mese, che 'ripeto, si trova nella colonna 3, e per ogni fine mese alla funzione "giorni"
ElseIf TextBox6.Value > Val(TextBox5) + 1 Then

'con la variabile X prendiamo la differenza in numero tra le textbox dei mesi
X = Val(TextBox6) - Val(TextBox5)
r = TextBox5.Value + 4 'identifica la riga del mese data di arrivo
c = Val(TextBox3) + 2
f = giorni(TextBox5) + 2
Range(Cells(r, c), Cells(r, f)).Interior.ColorIndex = 40

'inizia il ciclo che assegna a N il vettore X meno 1 (il primo mese)
For N = 1 To X - 1
d = 3 'con d reperiamo l'inizio del mese
fff = giorni(r + N + 1) + 2 'con fff usiamo la funzione "giorni" per reperire la fine mese. 'Usiamo r come contatore al quale aggiungiamo N + 1, + 2 (le due colonne che portano 'alla 3)

'quindi coloriamo tutte le celle del mese  
Range(Cells(r + N, d), Cells(r + N, fff)).Interior.ColorIndex = 40
Next

'sotto finiamo di reperire e di colorare la parte dall'inizio al giorno del mese 'corrispondente alla data di partenza
rr = TextBox6.Value + 4
cc = 3
ff = TextBox4.Value + 2
Range(Cells(rr, cc), Cells(rr, ff)).Interior.ColorIndex = 40

End If
End Sub

Sotto, un esempio che mostra l'effetto delle istruzioni sopra, con l'esecuzione della terza condizione, data di arrivo 14/04/03, data di partenza 10/08/03 (5 mesi) e il rispetto dei relativi fine mese.


Buon lavoro.


prelevato sul sito http://ennius.interfree.it