Excel vba creazione di un menu personalizzato

Riporto l'articolo scritto per il sito http://www.iprogrammatori.it/articoli/programmazione/art_excel-vba-creazione-di-un-menu-personali_1101.aspx

Introduzione
In questo articolo, vedremo, come creare un menu personalizzato tramite VBA nel programma di calcolo Excel 2010.
Vedremo come al click del pulsante destro, su un determinato testo, visualizza un menu personalizzato, anziché quello standard di Excel.
Il tutto come mostrato in figura 1.

Figura 1

Creazione del codice
Apriamo il programma Microsoft Excel 2010, e nella barra dei menu “Sviluppo”, selezioniamo la voce “Visual Basic”.
Nell’editor VBA, aggiungiamo una classe denomina “ClsWorkSheet”, il tutto come mostrato in figura 2.

Figura 2

Questa classe avrà il compito di gestire il foglio di calcolo e precisamente il tasto destro del mouse.
Qui di seguito si riporta il codice completo della classe.

Option Explicit
'oggetto relativo al worksheet
Dim WithEvents objWorkSheet As Worksheet
'imposto il worksheet
Property Set WorkSheetToMonitor(WS As Worksheet)
Set objWorkSheet = WS
End Property
'evento che si scatena quando si fa click con il tasto destro
Private Sub ObjWorkSheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'verifico il valore della casella di testo ed imposto il menu del popoup
Select Case LCase(Target.Text)
Case "roma"
ComBar_Roma.ShowPopup
Cancel = True
Case "catania"
ComBar_Catania.ShowPopup
Cancel = True
Case "reggio calabria"
ComBar_Reggio.ShowPopup
Cancel = True
Case Else
Cancel = False
End Select
End Sub

Aggiunta di un modulo
Aggiungiamo un modulo (denominato “MdlDichiarazione”) che avrà il compito di inizializzare il menu e gestire le varie operazioni. In particolare, in questo modulo si trova il codice che permette di generare il menu, di intercettare il valore della cena, ed eseguire le varie operazioni.
In effetti, a seconda del valore che andremo a leggere, verrà visualizzato una determinata voce di menu.
Per esempio, se nella cella è scritto il testo “Roma” visualizza una voce di menu con la scritta “Roma”, se c’è del testo “Catania” visualizzerà un menu con la scritta “Catania” e così via.
Qui di seguito si riporta il codice completo di tale modulo

Option Explicit
'variabii globali per gestire i popup
Global ComBar_Roma As CommandBar
Global ComBar_Catania As CommandBar
Global ComBar_Reggio As CommandBar
Global ColWorkSheet As Collection
Private objWorkSheetTrovato As Worksheet
'imposto per tutti le pagine, la classe worksheet per gestire il click destro del mouse
Sub ImpostaEventoWorkSheets()
Dim objWorkSheet As ClsWorkSheet
Set ColWorkSheet = Nothing
Set ColWorkSheet = New Collection
For Each objWorkSheetTrovato In ActiveWorkbook.Worksheets
Set objWorkSheet = New ClsWorkSheet
Set objWorkSheet.WorkSheetToMonitor = objWorkSheetTrovato
ColWorkSheet.Add objWorkSheet, objWorkSheetTrovato.Name
Next objWorkSheetTrovato
End Sub
'funzione che restituisce un oggetto di tipo commandBar
Function CreaMenuPopUp(strNomeCitta As String) As CommandBar
'oggetto command bar che viene restituito
Dim Combar As CommandBar
Dim ComBarControl As CommandBarControl
'rimuove eventuali istanze della barra
Call EliminaCommandBar(strNomeCitta)
'Aggiungo le varie barre
Set Combar = CommandBars.Add(Name:=strNomeCitta, _
Position:=msoBarPopup, _
MenuBar:=False, _
Temporary:=False)
'Aggiungo la voce di menu legando il testo della voce ed il metodo click sulla voce
Set ComBarControl = Combar.Controls.Add
With ComBarControl
'aggiungo la voce di menu
.Caption = "Voce menu 1 di: " & strNomeCitta
'aggiungo il metodo legato al click
.OnAction = "Messaggio"
End With
'creo una seconda voce di menu
Set ComBarControl = Combar.Controls.Add
With ComBarControl
.Caption = "Voce menu 2 di: " & strNomeCitta
.OnAction = "Messaggio"
End With
'restituisco il valore della commandbar creata
Set CreaMenuPopUp = Combar
Set ComBarControl = Nothing
Set Combar = Nothing
End Function
Sub EliminaCommandBar(strNomeBarra As String)
On Error Resume Next
CommandBars(strNomeBarra).Delete
End Sub
Sub Messaggio()
MsgBox "Menu: " & CommandBars.ActionControl.Caption, vbInformation, "Prova"
End Sub

Gestione dei menu
Siamo giunti quasi al termine dell’articolo, ora non ci resta che inizializzare i vari oggetti.
Sempre nell’editor VBA, troviamo nella finestra degli oggetti la voce “ThisWorkBook” o “Questa_cartella_di_lavoro”.
Facciamo doppio click su tale voce, si aprirà una finestra bianca, nella parte destra, selezioniamo “Workbook” e verrà generato in automatico l’evento open.
In esso, dobbiamo scrivere il codice per inizializzare il menu.
Qui di seguito si riporta il codice di questo evento.

Private Sub Workbook_Open()
'all'apertura del file Excel, imposto le varie voci di menu
Set ComBar_Roma = CreaMenuPopUp("Roma")
Set ComBar_Catania = CreaMenuPopUp("Catania")
Set ComBar_Reggio = CreaMenuPopUp("Reggio")
Call ImpostaEventoWorkSheets
End Sub

Conclusioni
Nel file excel, scriviamo nella prima cella (A1) il testo “Roma”, nella seconda cella a destra (B1) “Catania”, nella terza cella a destra (c1) “Reggio Calabria” e nella quarta cella (d1) “Milano”.
Ora non resta che salvare il nostro file, in formato “XLSM” ossia “cartella di lavoro con attivazione di macro Excel”, in questo modo verrà salvato il codice VBA.
Visto che il menu viene impostato all’apertura del file, dobbiamo chiudere e poi riaprire il file, ed eseguire la macro.
A questo punto, se facciamo click sulle varie voci vedremo il nostro menu personalizzato, questo menu non verrà visualizzato su altro testo, che sia diverso da quello del codice.
In allegato a questo articolo, trovate un esempio da scaricare.

Download