Parancsikon (jobb egérgombos) menük módosítása

Egy igen hasznos Excel jellegzetesség, hogy saját menüt, illetve menü-elemeket tudunk létrehozni. A VBA-ban a menüt CommandBar-nak hívjuk. Három fő területen tudjuk alkalmazni:

I. Felhasználói eszköztár (custom toolbar)
II. Felhasználói menü (custom menu)
III. Felhasználói parancsikon menük (custom shortcut menus) vagy más néven jobb egérgombbal előhívható menük (right click menus)

Az Excel 2007-es verziójától ha makrót írunk az I. és II. esetekhez, akkor a kódunk jó eséllyel megáll illetve nem fut le. Vagyis a CommandBar objektumnak igazából a III. esetben van értelme a 2007-es verziótól, így ezt fejtem ki részletesebben. Előbb azonban lássuk az érdekfeszítőbb részt animált gif-eken keresztül szemléltetve:

Parancsikon menühöz egy meglévő Excel funkció hozzáadása: itt az adatok rendezése vagyis sort data:

Parancsikon menühöz makró hozzáadása: itt az aktív munkafüzet elérési útja:

Parancsikon menük (shortcut menus):

Ennek a CommandBar típusa: msoBarTypePopUp
Kezdjük a parancsikon menük kilistázásával. Ehhez másoljuk be az alábbi kódot egy modulba és futtassuk le:

Sub ParancsikonMenuNevekOsszes()

Dim Sor As Long
Dim ComBar As CommandBar
Dim Lap As Worksheet

'Készítette: XLMotyo (https://xlmotyo.hu)

Sor = 1

'kitörli a "ParancsikonMenuLista" munkalapot ha létezik
For Each Lap In ThisWorkbook.Worksheets
    Application.DisplayAlerts = False
    If Lap.Name = "ParancsikonMenuLista" Then Lap.Delete
    Application.DisplayAlerts = True
Next Lap

'hozzáadja a "ParancsikonMenuLista" munkalapot
Sheets.Add(, Worksheets(Worksheets.Count)).Name = "ParancsikonMenuLista"

'Parancsikon menu neveinek kilistázása
For Each ComBar In CommandBars
    If ComBar.Type = msoBarTypePopup Then
        Cells(Sor, 1) = ComBar.Index
        Cells(Sor, 2) = ComBar.Name
        Sor = Sor + 1
    End If
Next ComBar

End Sub

Sub ParancsikonMenuNevekOsszes()

Dim Sor As Long

Dim ComBar As CommandBar

Dim Lap As Worksheet

'Készítette: XLMotyo (https://xlmotyo.hu)

Sor = 1

'kitörli a "ParancsikonMenuLista" munkalapot ha létezik

For Each Lap In ThisWorkbook.Worksheets

Application.DisplayAlerts = False

If Lap.Name = "ParancsikonMenuLista" Then Lap.Delete

Application.DisplayAlerts = True

Next Lap

'hozzáadja a "ParancsikonMenuLista" munkalapot

Sheets.Add(, Worksheets(Worksheets.Count)).Name = "ParancsikonMenuLista"

'Parancsikon menu neveinek kilistázása

For Each ComBar In CommandBars

If ComBar.Type = msoBarTypePopup Then

Cells(Sor, 1) = ComBar.Index

Cells(Sor, 2) = ComBar.Name

Sor = Sor + 1

End If

Next ComBar

End Sub

Eredmény (részlet):

Megkülönböztetünk CommandBar (egyes számban) objektumot és CommandBars (többes számban) gyűjteményt:

CommandBars gyűjtemény:

az Application objektum tagja. Ha a ThisWorkbook objektumban írunk kódot, akkor a teljes hivatkozást kell használni:

MsgBox Application.CommandBars("Cell").Name, , ""

1	MsgBox Application.CommandBars("Cell").Name, , ""

Egyéb esetekben az Application szót elhagyhatjuk a kódunkban, amint azt fentebb is említettem:

MsgBox CommandBars("Cell").Name, , ""

1	MsgBox CommandBars("Cell").Name, , ""

CommandBar objektum:

1. kétféleképpen hivatkozhatunk rá: Index szám vagy Név tulajdonság alapján.
Az Indexszám sajnálatos módon nem állandó a különböző Excel verziók esetében. Vagyis pl. a cellának az index értéke (fenti képernyőmentésen is megtalálható) Office 365-ös Excelnél: 39 és 42. Kód:

MsgBox CommandBars(39).Name, , ""

1	MsgBox CommandBars(39).Name, , ""

Az eredmény itt a „cell”, mint cella lesz. Ugyanez Excel 2010-nél: 35 és 38.

Éppen ezért tanácsosabb az indexszám helyett a nevet használni:

MsgBox CommandBars("Cell").Name, , ""

1	MsgBox CommandBars("Cell").Name, , ""

2. Control objektumokat tartalmaz, melyek lehetnek: gombok vagy menük. Példának okáért az előbb említett Cella parancsikon (amikor jobb egérgombbal kattintunk egy cellán) menüjének indexszámait és neveit így kaphatjuk meg (másoljuk be a kódot egy modulba és futtassuk le):

Sub CellaParancsikonMenuNevek()

Dim Szoveg As String
Dim ComBarCTL As CommandBarControl

'Készítette: XLMotyo (https://xlmotyo.hu)

'a kiírandó szöveg nyelve függ az Excel megjelenítési nyelvétõl (magyar nyelvû Excel esetén magyar lesz a lista)

For Each ComBarCTL In CommandBars("Cell").Controls
    Szoveg = Szoveg & ComBarCTL.Index & ":" & vbTab & ComBarCTL.Caption & vbNewLine
Next ComBarCTL

MsgBox Szoveg, , "Cella parancsikon menü indexszámok és nevek"

End Sub

Sub CellaParancsikonMenuNevek()

Dim Szoveg As String

Dim ComBarCTL As CommandBarControl

'Készítette: XLMotyo (https://xlmotyo.hu)

'a kiírandó szöveg nyelve függ az Excel megjelenítési nyelvétõl (magyar nyelvû Excel esetén magyar lesz a lista)

For Each ComBarCTL In CommandBars("Cell").Controls

Szoveg = Szoveg & ComBarCTL.Index & ":" & vbTab & ComBarCTL.Caption & vbNewLine

Next ComBarCTL

MsgBox Szoveg, , "Cella parancsikon menü indexszámok és nevek"

End Sub

Eredmény:

Az összefűzés („&”) jel utáni betű a szövegben lévő aláhúzott betűt jelenti, vagyis azt a billentyűparancsot, mellyel az utasítás végrehajtható a munkalapon. Itt pl. a fenti képen a „Ki&vágás”-nál a „v” betű lesz ez a betű. Ellenőrizzük le a Cella parancsikon (amikor jobb egérgombbal kattintunk egy cellán) menüjében:
Kattintsunk jobb egérgombbal egy cellán, majd nézzük meg a „Kivágás” opciót:

Látható, hogy a „Kivágás” szóban a „v” betű aláhúzott, vagyis a jobb egérgombbal való kattintás után a „v” betű lenyomásával is elindíthatjuk a kivágást.

3. Néhány esetben a Control objektumok további Control objektumokat tartalmazhatnak. Pl. ha a cellán jobb egérgombbal kattintunk, akkor a Szűrő (Filter) további lehetőségeket tartalmaz. A szűrő tételeit az alábbi makróval kaphatjuk meg (másoljuk be a kódot egy modulba és futtassuk le):

Sub ParancsikonMenuNevek_Szures_Blogra()

Dim ComBarCTL As CommandBarControl

'Készítette: XLMotyo (https://xlmotyo.hu)

'1. a "Cell" (azaz Cella) felhasználó parancsikon menü "Filter" (azaz Szûrõ) almenüjének indexszámát ki kell derítenünk -
'ehhez futtassuk a "Sub CellaParancsikonMenuNevek" makrót (itt ez az indexszám a 15-ös lesz a Filter-hez)

'2. a "Cell" (azaz Cella) felhasználó parancsikon menü "Filter" (azaz Szûrõ) almenüjének összes eleme:

For Each ComBarCTL In CommandBars("Cell").Controls(15).Controls 'a Controls("Szur&o") nem mûködik, csak a Controls(15)
    MsgBox ComBarCTL.Caption, , ""
Next ComBarCTL

End Sub

Sub ParancsikonMenuNevek_Szures_Blogra()

Dim ComBarCTL As CommandBarControl

'Készítette: XLMotyo (https://xlmotyo.hu)

'1. a "Cell" (azaz Cella) felhasználó parancsikon menü "Filter" (azaz Szûrõ) almenüjének indexszámát ki kell derítenünk -

'ehhez futtassuk a "Sub CellaParancsikonMenuNevek" makrót (itt ez az indexszám a 15-ös lesz a Filter-hez)

'2. a "Cell" (azaz Cella) felhasználó parancsikon menü "Filter" (azaz Szûrõ) almenüjének összes eleme:

For Each ComBarCTL In CommandBars("Cell").Controls(15).Controls 'a Controls("Szur&o") nem mûködik, csak a Controls(15)

MsgBox ComBarCTL.Caption, , ""

Next ComBarCTL

End Sub

A felhasználói parancsikonokat le is lehet tiltani, például megadhatjuk, hogy a cellán való jobb egérgombos kattintással ne jelenjen meg a menü (másoljuk be a kódot egy modulba és futtassuk le):

Sub CellaParancsikonMenuLetiltas()

Dim cbar As CommandBar

'Készítette: XLMotyo (https://xlmotyo.hu)

For Each cbar In Application.CommandBars
    If cbar.Name = "Cell" Then cbar.Enabled = False
    'If cbar.Name = "Cell" Then cbar.Enabled = True
Next cbar

End Sub

Sub CellaParancsikonMenuLetiltas()

Dim cbar As CommandBar

'Készítette: XLMotyo (https://xlmotyo.hu)

For Each cbar In Application.CommandBars

If cbar.Name = "Cell" Then cbar.Enabled = False

'If cbar.Name = "Cell" Then cbar.Enabled = True

Next cbar

End Sub

A felhasználói parancsikonok elemeit is le lehet tiltani, pl. a Szűrő-nél a „Szűrés a kijelölt cella színe alapján” elemet:
Alapesetben ez az elem használható: kattintsunk jobb egérgombbal egy cellán:

Ha azonban az alábbi kódot lefuttatjuk (másoljuk be a kódot egy modulba és futtassuk le) ….

Sub ParancsikonMenu_Szures_TetelLetiltasa()

Dim ComBarCTL As CommandBarControl

'Készítette: XLMotyo (https://xlmotyo.hu)

For Each ComBarCTL In CommandBars("Cell").Controls(15).Controls
    If ComBarCTL.Caption Like "*szín*" And Not ComBarCTL.Caption Like "*betû*" Then
        ComBarCTL.Enabled = False 'letiltás
        'ComBarCTL.Enabled = True 'újbóli engedélyezés
    End If
Next ComBarCTL

End Sub

Sub ParancsikonMenu_Szures_TetelLetiltasa()

Dim ComBarCTL As CommandBarControl

'Készítette: XLMotyo (https://xlmotyo.hu)

For Each ComBarCTL In CommandBars("Cell").Controls(15).Controls

If ComBarCTL.Caption Like "*szín*" And Not ComBarCTL.Caption Like "*betû*" Then

ComBarCTL.Enabled = False 'letiltás

'ComBarCTL.Enabled = True 'újbóli engedélyezés

End If

Next ComBarCTL

End Sub

… akkor inaktív lesz a szóban forgó elem, vagyis nem tudjuk használni:

Ami azonban igazán látványos – és legalább olyan hasznos – az az elemek hozzáadása a parancsikon menühöz. Hozzá tudunk ugyanis adni meglévő Excel funkciókat és makrókat (lásd az animációkat a bejegyzés elején).

1. Meglévő Excel funkciók hozzáadása:

Funkciók alatt itt az Excel beépített lehetőségeit értem, mint például a sorba rendezés (Adatok menü – Rendezés)

Az alábbi makró „ThisWorkbook”-ba történő bemásolása után…

Private Sub Workbook_Deactivate()

'Készítette: XLMotyo (https://xlmotyo.hu)

On Error Resume Next
With Application
    .CommandBars("Cell").FindControl(ID:=928).Delete '928: Adatok menü - Rendezés parancs azonosítója
End With
On Error GoTo 0

End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dim cmdBtn As CommandBarButton

'Készítette: XLMotyo (https://xlmotyo.hu)

On Error Resume Next
With Application
    .CommandBars("Cell").FindControl(ID:=928).Delete '928: Adatok menü - Rendezés parancs azonosítója
    Set cmdBtn = .CommandBars("Cell").Controls.Add(ID:=928, Before:=1, Temporary:=True)
End With

With cmdBtn
   .Caption = "Sort Data"
   .Style = msoButtonCaption
End With
On Error GoTo 0

End Sub

Private Sub Workbook_Deactivate()

'Készítette: XLMotyo (https://xlmotyo.hu)

On Error Resume Next

With Application

.CommandBars("Cell").FindControl(ID:=928).Delete '928: Adatok menü - Rendezés parancs azonosítója

End With

On Error GoTo 0

End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dim cmdBtn As CommandBarButton

'Készítette: XLMotyo (https://xlmotyo.hu)

On Error Resume Next

With Application

.CommandBars("Cell").FindControl(ID:=928).Delete '928: Adatok menü - Rendezés parancs azonosítója

Set cmdBtn = .CommandBars("Cell").Controls.Add(ID:=928, Before:=1, Temporary:=True)

End With

With cmdBtn

.Caption = "Sort Data"

.Style = msoButtonCaption

End With

On Error GoTo 0

End Sub

… ha jobb egérgombbal kattintunk egy cellán, akkor az első opció a „Sort Data” lesz:

Ennek használata ugyanaz, mintha az Adatok menün belül a Rendezés-t választanánk. Viszont az elérése jóval gyorsabb és kényelmesebb.

2. Makrók hozzáadása:

Tegyük fel, hogy az aktív munkafüzet helyét gyakran le kell ellenőriznünk. Az ezt lehetővé tévő kód mindössze egy sor:

MsgBox ActiveWorkbook.Path & Application.PathSeparator, vbInformation, ""

1	MsgBox ActiveWorkbook.Path & Application.PathSeparator, vbInformation, ""

Ezt a makrót is hozzá tudjuk adni a cella parancsikon menüjéhez:

Ez a kód megy a “ThisWorkbook”-ba:

Private Sub Workbook_Deactivate()

'Készítette: XLMotyo (https://xlmotyo.hu)

On Error Resume Next
With Application
    .CommandBars("Cell").Controls("Aktív munkafüzet elérési útja").Delete
End With
On Error GoTo 0

End Sub
 
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dim cmdBtn As CommandBarButton

'Készítette: XLMotyo (https://xlmotyo.hu)

On Error Resume Next
With Application
    .CommandBars("Cell").Controls("Aktív munkafüzet elérési útja").Delete
    Set cmdBtn = .CommandBars("Cell").Controls.Add(, , , Before:=1, Temporary:=True)
End With

With cmdBtn
   .Caption = "Aktív munkafüzet elérési útja"
   .Style = msoButtonCaption
   .OnAction = "AktivMunkafuzetHelye"
End With
On Error GoTo 0

End Sub

Private Sub Workbook_Deactivate()

'Készítette: XLMotyo (https://xlmotyo.hu)

On Error Resume Next

With Application

.CommandBars("Cell").Controls("Aktív munkafüzet elérési útja").Delete

End With

On Error GoTo 0

End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dim cmdBtn As CommandBarButton

'Készítette: XLMotyo (https://xlmotyo.hu)

On Error Resume Next

With Application

.CommandBars("Cell").Controls("Aktív munkafüzet elérési útja").Delete

Set cmdBtn = .CommandBars("Cell").Controls.Add(, , , Before:=1, Temporary:=True)

End With

With cmdBtn

.Caption = "Aktív munkafüzet elérési útja"

.Style = msoButtonCaption

.OnAction = "AktivMunkafuzetHelye"

End With

On Error GoTo 0

End Sub

Ezt a kódot pedig egy modulba másoljuk bele:

Sub AktivMunkafuzetHelye()
'Készítette: XLMotyo (https://xlmotyo.hu)
MsgBox ActiveWorkbook.Path & Application.PathSeparator, vbInformation, ""
End Sub

Sub AktivMunkafuzetHelye()

'Készítette: XLMotyo (https://xlmotyo.hu)

MsgBox ActiveWorkbook.Path & Application.PathSeparator, vbInformation, ""

End Sub

Az fenti makrók bemásolása után megjelenik a makró neve, ha jobb egérgombbal kattintunk egy cellán:

Ha rákattintunk, akkor az aktív munkafüzet elérési útját fogjuk látni:

Ha parancsikon menühöz elemeket adunk hozzá Excel-ben, akkor ajánlott ezeket az elemeket kitörölni mikor a munkafüzetet bezárjuk.

Remélem hasznos volt a poszt.
Mintafájlért illetve kérdésekkel kapcsolatosan szólj hozzá lent vagy dobj egy emailt: xlmotyo@gmail.com

(Inspiráció: John Walkenbach: „Excel® 2010 Power Programming with VBA” című könyve.)

Parancsikon (jobb egérgombos) menük módosítása

Egy igen hasznos Excel jellegzetesség, hogy saját menüt, illetve menü-elemeket tudunk létrehozni. A VBA-ban a menüt CommandBar-nak hívjuk. Három fő területen tudjuk alkalmazni:

I. Felhasználói eszköztár (custom toolbar) II. Felhasználói menü (custom menu) III. Felhasználói parancsikon menük (custom shortcut menus) vagy más néven jobb egérgombbal előhívható menük (right click menus)

Parancsikon menük (shortcut menus):

CommandBars gyűjtemény:

az Application objektum tagja. Ha a ThisWorkbook objektumban írunk kódot, akkor a teljes hivatkozást kell használni:

CommandBar objektum:

2. Control objektumokat tartalmaz, melyek lehetnek: gombok vagy menük. Példának okáért az előbb említett Cella parancsikon (amikor jobb egérgombbal kattintunk egy cellán) menüjének indexszámait és neveit így kaphatjuk meg (másoljuk be a kódot egy modulba és futtassuk le):

Ami azonban igazán látványos – és legalább olyan hasznos – az az elemek hozzáadása a parancsikon menühöz. Hozzá tudunk ugyanis adni meglévő Excel funkciókat és makrókat (lásd az animációkat a bejegyzés elején).

1. Meglévő Excel funkciók hozzáadása:

2. Makrók hozzáadása:

Remélem hasznos volt a poszt. Mintafájlért illetve kérdésekkel kapcsolatosan szólj hozzá lent vagy dobj egy emailt: xlmotyo@gmail.com

Ajánló

I. Felhasználói eszköztár (custom toolbar)
II. Felhasználói menü (custom menu)
III. Felhasználói parancsikon menük (custom shortcut menus) vagy más néven jobb egérgombbal előhívható menük (right click menus)

Remélem hasznos volt a poszt.
Mintafájlért illetve kérdésekkel kapcsolatosan szólj hozzá lent vagy dobj egy emailt: xlmotyo@gmail.com