Finding Right Click Menus
The menus you get when you right click in Excel are toolbars (commandbars) just like the Standard and Formatting toolbars or the menu at the top of the application. If you know their names, you can manipulate them just like any commandbar object.
This sub will list in Sheet1 all of the commandbars that are of Type Popup (that is, they are right click menus)
Sub ListAllRightClicks()
Dim cBar As CommandBar
Dim i As Long
For Each cBar In Application.CommandBars
If cBar.Type = msoBarTypePopup Then
i = i + 1
Sheet1.Cells(i, 1).Value = cBar.Name
End If
Next cBar
End Sub
The commandbar you get when you right click on a cell is called “Cell”. That one I can remember. Some of the others are pretty obvious based on thier names. And some…not so much. I’ve never seen a good way to figure out the names of right click menus, so I’ll show you the bad way (read: the way I do it).
Start with a class module called CCBarEvent and this code:
Dim WithEvents mTool As CommandBarButton
Property Get Tool() As CommandBarButton
Set Tool = mTool
End Property
Property Set Tool(aTool As CommandBarButton)
Set mTool = aTool
End Property
Private Sub mTool_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
CancelDefault = True
Debug.Print Ctrl.Caption, Ctrl.Parent.Name
End Sub
Now a procedure to load up the classes - there will be 413 of them when your done if you use Excel 2000. This uses a Public array variable to hold the class instances.
Sub InstantiateClass()
Dim cBar As CommandBar
Dim cCntl As CommandBarControl
Dim i As Long
For Each cBar In Application.CommandBars
If cBar.Type = msoBarTypePopup Then
For Each cCntl In cBar.Controls
If TypeName(cCntl) = “CommandBarButton” Then
i = i + 1
ReDim Preserve clsCBarEvent(1 To i)
Set clsCBarEvent(i) = New CCBarEvent
Set clsCBarEvent(i).Tool = cCntl
End If
Next cCntl
End If
Next cBar
End Sub
This takes every commandbarbutton from every popup toolbar and creates a class to capture when it’s clicked. Now you can go click crazy in Excel to see which right clicks return which toolbars/controls. You have to click a commandbutton type control, not a popup or combobox or anything like that.
Here I clicked on the Pick From List control after right clicking in a cell, the Select All Sheets control after right clicking on a sheet tab, and the Edit Text control after right clicking on a CheckBox from the Forms toolbar. Note that the Pick From List is listed twice. That control exists on two popup commandbars, so those two classes were called. Because I use the Ctrl variable in the event, I get the same answer for both. Try clicking on the Cut button and you’ll get about a dozen entries. You could code it to add them to a collection to remove duplicates, but I’m not that ambitious.
When you’re done with your investigation, release all those classes so you can get the buttons’ functionality back.
Sub DestroyClass()
Erase clsCBarEvent
End Sub
That’s the brute force method of getting popup menu names. It’s not pretty, but it works. Most of the time, a quick review of the names of all the popups will reveal the answer, but if you guessed that Ply was the name of the menu you get from right clicking on a sheet tab, then you’re smarter than me.
Update: It’s funny that when you revisit old code, like above, that you wonder whatever possessed you to write it. I know that I needed the names of those commandbars, and I’m sure the path I took to get to 413 class instances was completely logical. So how did it ever turn out so complicated?
I was lying in bed thinking about how all this complexity should really be in an add-in. I was thinking about how it would work and so on. Then it hit me. I could do the same thing about 1000 times easier. Most of you probably already know an easier way (and already use it), but for those that don’t, I’ll let you think about it and hold that post until tomorrow.
This code gives you a list of the controls available on the popup menu; from this I can (mostly) figure out which popup it is (I’ve never seen the Curve Node popup!).
My first version had the captions being listed one in each column to the right of the cBar.Name, but it was too wide.
Sub ListAllRightClicks2()
Dim cBar As CommandBar
Dim cCtrl As CommandBarControl
Dim i As Long
Dim strControls As String
For Each cBar In Application.CommandBars
If cBar.Type = msoBarTypePopup Then
i = i + 1
strControls = “”
Sheet1.Cells(i, 1).Value = cBar.Name
For Each cCtrl In cBar.Controls
strControls = strControls _
& vbLf & cCtrl.Caption
Next
‘remove the first vbLf
strControls = Mid(strControls, 2)
Sheet1.Cells(i, 2).Value = strControls
End If
Next cBar
With Range(”A:B”)
.VerticalAlignment = xlVAlignTop
.Columns.AutoFit
.Rows.AutoFit
End With
End Sub
bloody hell Dick, that’s a long post!!! I’ve skipped through it, so sorry if i’m off the beat here
I was playing around with this like yesterday!
this is what i done
get all the names for the CB.
Sub tim()
Dim i As Integer
Dim x As String
For i = 1 To CommandBars.Count
x = CommandBars(i).Name
Debug.Print i & ” ” & x
Next
MsgBox “done”
End Sub
Which one does it look like? test it, i use dual screen, but you could split you display, or just use tab etc.
Sub show() ”’ or run in imtermidiate
CommandBars(27).Visible
End Sub
Any way i bet i’m missing somthing, but it got the job done that i wanted. “ply” BTW, for the tab pop up, not as you might expect “tab”….humm, i always use the number, just in case. I thing i read something on J-walk about this being better incase the xl lang is diffrent, i bet diffrent version use diffrent numbers as well….. oh the joys od excellllll,
ok, i think i’ve got it now, sorry, - but why would you want too??? do you want to disable some of the options maybe?
(I’ve never seen the Curve Node popup!)
I have!
http://tinypic.com/nqbu1
note that i have changed the method i used, in my first case to test my hun, i guess it will only work on the 1st layer of menu’s.
how are people finding ” and ‘ ’s?
Re: Curve Node popup
I guess I’ve never even thought of using Excel to draw stuff in. I’ve always had CorelDraw or Illustrator or other vector graphic software available.
Mind you, I had an needlework instructor who used to plot her canvaswork designs in Excel!
Here’s a extreme example of drawing in Excel from J-Walk’s site.
http://j-walk.com/ss/excel/odd/odd22.htm
>> I had an needlework instructor who used to plot her canvaswork designs in Excel! < <
I wrote a little macro for a PowerPoint MVP who wanted to make the cells in Excel small and square to use as a template for needlepoint designs.
Sub SquareCells()
Dim i As Integer
For i = 1 To 4
With ActiveSheet
.Columns.ColumnWidth = _
.Columns("A").ColumnWidth / _
.Columns("A").Width * _
.Rows(1).Height
End With
Next
End Sub
You need to loop a few times to encourage Excel to size the columns with such precision.
>> I had an needlework instructor who used to plot her canvaswork designs in Excel!
(Somehow this paragraph got deleted between Submit and page refresh)
I answered a question from a PowerPoint MVP who wanted to make tiny square cells as a template for her needlepoint designs.
Make the rows the height you need, and run this macro.
Sub SquareCells() Dim i As Integer For i = 1 To 4 With ActiveSheet .Columns.ColumnWidth = _ .Columns("A").ColumnWidth / _ .Columns("A").Width * _ .Rows(1).Height End With Next End SubYou need to loop a few times to encourage Excel to size the columns with such precision.
Thank you all for this post, 4 years later and its still helpful. I thought I would expand the post by adding the actual list of commandbars I found. The “Cell” command bar didn’t do it for me, I was trying to add to the right click menu that was shown while inside a table. Oddly enough “Query” was the right one. If you run the code in the comments above it will help you figure out which ones you need.
PivotChart Menu
Workbook tabs
Cell
Column
Row
Cell
Column
Row
Ply
XLM Cell
Document
Desktop
Nondefault Drag and Drop
AutoFill
Button
Dialog
Series
Plot Area
Floor and Walls
Trendline
Chart
Format Data Series
Format Axis
Format Legend Entry
Formula Bar
PivotTable Context Menu
Query
Query Layout
AutoCalculate
Object/Plot
Title Bar (Charting)
Layout
Pivot Chart Popup
Phonetic Information
Auto Sum
Paste Special Dropdown
Find Format
Replace Format
List Range Popup
List Range Layout Popup
XML Range Popup
List Range Layout Popup
Nil
Filter Names
Excel Previewer
Shapes
Inactive Chart
Excel Control
Curve
Curve Node
Curve Segment
Pictures Context Menu
OLE Object
ActiveX Control
WordArt Context Menu
Rotate Mode
Connector
Script Anchor Popup
Canvas Popup
Organization Chart Popup
Diagram
Add Command
Built-in Menus
Layout
Select