Usare le celle del foglio come pulsanti (Target)

 

Alle volte possiamo desiderare di attivare le nostre routines senza dover usare dei pulsanti prelevati dalla barra Moduli o dalla barra Strumenti di controllo ma selezionando semplicemente una cella del foglio.

La cosa è possibile in quanto il nostro affabile VBA ci viene in aiuto offrendoci la possibilità di lavorare sugli eventi. Ma prima di affrontare il tema occorre un po' di teoria per capire meglio quel che andremo a fare.

In VB e VBA ad ogni azione che compiamo, che noi lo vogliamo o no, corrisponde un determinato evento. Molte volte non ce ne rendiamo conto perchè non ci sono istruzioni personalizzate nei singoli eventi per cui non riusciamo a renderci conto di quel che sta succedendo in realtà perchè questi eventi passano inosservati.

Una procedura di evento entra in azione automaticamente come reazione di un'azione dell'utente o di istruzioni scritte nel codice e che compiono azioni al posto dell'utente. Questo evento può essere per esempio la selezione di una cella, il cambiamento di un valore, il click del mouse, l'apertura di una cartella, l'attivazione di un foglio di calcolo o altro.

Questa volta andremo ad esaminare i seguenti due eventi:

Possiamo usare l'argomento Target legato a questi eventi proprio come siamo abituati ad usare l'oggetto Range. Anche il suo comportamento nell'editor VBA è simile a quello di Range, come è visibile in questa immagine ripresa durante la digitazione di Range ed in quest'altra ripresa durante la digitazione di Target.

Io a questo punto farei una distinzione nella scelta tra i due eventi:

Occorre, tuttavia, usare alcune precauzioni sull'uso di Target più che sull'uso di Range.

Evitare la selezione di celle multiple

Alcune volte lavorare su celle multiple può causare errori e blocchi dell'applicazione.Quando usiamo Range sappiamo quel che scriviamo e, nella maggior parte dei casi, quel che stiamo facendo. Ma lavorando con Target di questi due eventi cipotrebbe causare dei seri problemi.

Quando dobbiamo verificare se il Target si riferisce a più celle possiamo contare le celle coinvolte mediante la Proprietà Count come in questi esempi:

If Target.Count > 1 Then Exit Sub

oppure, per togliere la selezione miltipla:

If Target.Count > 1 Then ActiveCell.Select

(tanto per chiarire, la cella attiva è quella da cui parte l'azione della selezione e che dopo la selezione rimane bianca.)

Dopo aver eseguito questa verifica è possibile scrivere il resto del codice. Per esempio:

If Target.Count > 1 Then Exit Sub
Target = UCase(Target)

Evitare l'esecuzione della procedura di evento

Lavorando con procedure di evento può succedere che una volta attivata non ci sia più modo di interromperla a meno di ricorrere alla pressione del tasto Esc o della combinazione di tasti Control + Bloc.

A questo si può ovviare in due maniere:

Per la prima maniera si può intervenire come mostrato negli esempi illustrati qui sopra

Per la seconda maniera si può ricorrere alla Proprietà EnableEvents ponendola a False all'inizio delle istruzioni da contenere ed a True alla fine.

Le seguenti istruzioni, scritte nel modulo del foglio1 usano l'evento Worksheet_Change e sembrano corrette. Tuttavia vi accorgerete che l'esecuzione della routine, una volta avviata con l'inserimento di un nuovo valore in una cella, non finirà più nel tentativo di riempire tutta la riga a meno che ci decidiamo a premere il tarto ESC o la combinazione CONTROL + Bloc.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsDate(Target) Then Target.NumberFormat = "General"
Target.Offset(0, 1) = Target.NumberFormat
Target.Offset(0, 2) = Target.Row
Target.Offset(0, 3) = Target.Column
Target.Offset(0, 4) = VarType(Target)
Target.Offset(0, 5) = TypeName(Target)
End Sub

Per non incorrere in questo errore occorre usare la Proprietà EnableEvents come mostrato nel seguente codice opportunamente modificato:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not IsDate(Target) Then Target.NumberFormat = "General"
Target.Offset(0, 1) = Target.NumberFormat
Target.Offset(0, 2) = Target.Row
Target.Offset(0, 3) = Target.Column
Target.Offset(0, 4) = VarType(Target)
Target.Offset(0, 5) = TypeName(Target)
Application.EnableEvents = True
End Sub

 

Non eseguire le istruzioni se si verificano determinate situazioni

Questo è un altro esempio che non funzionerà o ci farà impazzire in un foglio dove intendiamo lavorare con l'evento Il seguente codice potrebbe sembrare corretto.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> "Antonio" Then
Target.ClearContents
Target.Select
MsgBox "Inserire Antonio"
End If
End Sub

Purtroppo in questa circostanza se il valore scritto non è Antonio verrà a crearsi un loop pressoché infinito perchè anche dopo la cancellazione del nome che non è Antonio la cella rimasta vuota sarà sempre diversa da Antonio.

Per evitare questo fatto sgradevole dovuto a queste chiamate ricorsive, è possibile modificare l'istruzione appena vista in una delle seguenti.

Qui viene controllata se la cella è vuota. In questo caso si esce senza altro fare

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target <> "Antonio" Then
Target.ClearContents
Target.Select
MsgBox "Inserire Antonio"
End If
End Sub

In quest'altro modo viene semplicemente inibita l'esecuzione di qualsiasi evento conseguente alla cancellazione del contenuto della cella

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> "Antonio" Then
Application.EnableEvents = False
Target.ClearContents
Target.Select
MsgBox "Inserire Antonio"
Application.EnableEvents = True
End If
End Sub

Vediamo un altro esempio pratico

In questo esempio è possibile selezionare solo una determinata cella alla volta, per cui viene disabilitata la selezione di celle che non siano quella in cui vogliamo che l'utente scriva

Per eseguire questa applicazione avremmo bisogno dei due eventi del foglio di lavoro:

col primo verifichiamo se ci troviamo nella giusta cella verificando tutte le possibili condizioni viste qui sopra.

E' da notare la istruzione Exit Sub dopo l'ultima If e prima del 10:

Serve a non far eseguire inutilmente le istruzioni che seguono l'etichetta 10:

col secondo verifichiamo, dopo l'inserimento, che il dato inserito sia un numero

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Intervallo As Range
Dim RigaValida
Set Intervallo = Range("D3")
RigaValida = Intervallo.CurrentRegion.Rows.Count
' verifica di una selezione multipla
If Target.Count > 1 Then
MsgBox "Vietate selezioni multiple"
GoTo 10
End If
' verifica della giusta locazione
If Target.Column <> 4 Or Target.Row < 3 Then
MsgBox "Non è la locazione giusta"
GoTo 10
End If
' verifica se la cella selezionata contiene già i dati
If Not IsEmpty(Target) Then
MsgBox "Valore già inserito"
GoTo 10
End If
' controlla se la riga superiore è vuota
If Target.Row > 3 And Target.Offset(-1, 0) = "" Then
MsgBox "Sei troppo in basso"
GoTo 10
End If
Exit Sub

10:
If RigaValida = 1 And Intervallo = "" Then
Application.EnableEvents = False
Intervallo.Select
Application.EnableEvents = True
Exit Sub
Else
Application.EnableEvents = False
Intervallo(RigaValida + 1, 1).Select
Application.EnableEvents = True
Exit Sub
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If VarType(Target) = vbString Then
MsgBox "non è lecito scrivere valori letterali"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
End Sub

Qui sopra vediamo le due routines di evento all'opera.

Ho evidenziato come è stata usata la Proprietà EnableEvents e la insolita, almeno per me, GoTo 10 usata nella prima routine di evento ed il metodo usato per verificare il tipo di dato inserito nella cella nella seconda routine di evento.

 

Le celle del foglio come pulsanti (Target)

Dopo questa doverosa premessa posso passare con animo tranquillo all'esposizione del mio progetto.

Lavorando sull'evento Worksheet_SelectionChange si può far in modo che con la selezione di una particolare cella venga attivata una determinata routine, proprio come se usassimo i classici pulsanti a cui siamo abituati. Prendiamo come esempio una porzione di foglio che ho usato in un mio lavoro e che ho collocato in una posizione in cui non da fastidio alle tabelle che debbono essere inserite nel foglio.

La cosa è più semplice di quanto possa sembrare. Basta dire al codice che vogliamo utilizzare l'evento Worksheet_SelectionChange solo quando viene selezionata una determinata cella per far partire una determinata routine che abbiamo preparato magari in un Modulo standard.

Ma prima di andare avanti scriviamo le routines che vogliamo attivare quando selezioniamo una determinata cella del foglio.

Qui ne mostro solo due alle quali sono associate altrettante celle del foglio di lavoro (nel nostro esempio le celle sono la "M3" e la "M5".

 

Questa la routine che stampa la tabellina sul foglio:

Sub LaTabellina()
Dim Matrice(1 To 10, 1 To 10)
Dim Intervallo As Range
Dim Righe, Colonne
For Righe = 1 To 10
For Colonne = 1 To 10
Matrice(Righe, Colonne) = Righe * Colonne
Next
Next
Range("A1").CurrentRegion.ClearContents
' qui il calcolo delle dimensioni dell'intervallo che dovrà ospitare la tabellina
Righe = UBound(Matrice, 1)
Colonne = UBound(Matrice, 2)
Set Intervallo = Range("A1").Resize(Righe, Colonne)
Application.EnableEvents = False
Intervallo.CurrentRegion.ClearContents
'dopo la cancellazione dell'intervallo riversiamo tutto il contenuto
'della matrice senza far uso di alcun ciclo
Intervallo = Matrice
'dopo aver riversato tutti i dati nell'intervallo
'adattiamo la larghezza delle colonne al contenuto
Intervallo.Columns.AutoFit
Intervallo(1).Select ' viene selezionata la prima cella dell'intervallo per togliere la selezione precedente
Application.EnableEvents = True
End Sub

 

Questa la routine che copia sul foglio attivo una tabella che si trova in altro foglio:

Sub CopiadaAltroFoglio()
Dim Intervallo As Range
Dim Intervallo2 As Range
Dim Righe, Colonne
With Worksheets("Foglio6").Range("A1").CurrentRegion
Righe = .Rows.Count
Colonne = .Columns.Count
Set Intervallo = .Resize(Righe, Colonne)
End With
' viene creato sul foglio attivo un intervallo uguale a quello rilevato sul foglio6
Set Intervallo2 = Range("A1").Resize(Righe, Colonne)
Application.EnableEvents = False
Intervallo2.CurrentRegion.ClearContents
' viene copiato nell'intervallo del foglio corrente il contenuto di intervallo2 anche qui senza eseguire cicli
Intervallo.Copy Intervallo2
' vengono aggiustate le dimensioni delle celle sulla larghezza dei contenuti
Intervallo2.Columns.AutoFit
Intervallo2(1).Select
Application.EnableEvents = True
End Sub

 

Nelle precedenti istruzioni possiamo notare, tra le altre cose,

la presenza delle istruzioni per disabilitare ed abilitare gli eventi che le istruzioni poste nel loro corpo potrebbero far scatenare (Application.EnableEvents = False e Application.EnableEvents = True)

la presenza del Metodo AutoFit usato per adattare le colonne della tabella alla larghezza dei dati e che è l'equivalente del comando che diamo in Excel dal menù Formato ----> Colonna ---> Adatta

 

L'uso dell'evento Worksheet_SelectionChange del foglio di lavoro di cui usiamo le celle come pulsanti

Scritte le routine di cui abbiamo intenzione di far uso, siamo pronti a stabilire le celle che debbono fungere da pulsanti ed a preparare la routine nel modulo del foglio interessato usando l'evento Worksheet_SelectionChange.

Le celle scelte per questo lavoro sono la "M3" e la "M5".

la routine da scrivere potrebbe essere questa:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Indirizzo
' non si possono selezionare più celle
If Target.Count > 1 Then
ActiveCell.Select
Exit Sub
End If
' viene rivelata la cella selezionata
Indirizzo = Target.Address(0, 0)
Select Case Indirizzo
Case "M3"
Range("A1").Select
LaTabellina ' viene richiamata la routine che stampa la tabellina
Case "M5"
Range("A1").Select
CopiadaAltroFoglio ' viene richiamata la routine che copia una tabella da un altro foglio
End Select
End Sub

 

Conclusioni

Gli esempi sopra descritti possono rivelarsi un valido aiuto e facilmente implementati in applicazioni di più vaste portate. E' ovvio che specialmente quest'ultimo esempio può essere ampliato ed invece delle routines presentate si possono abbinare quante celle vogliamo ad altrettante routines di cui abbiamo bisogno.

Perciò ora non mi resta che augurare buon lavoro ai volenterosi che vogliono approfondire l'argomento trattato.