Find Excel’s VBE Command Bar names and built-in controls IDs. – VBA and VB.Net
As requested by Eric in the comments of this post, here is a code that I wrote couple of years ago which can help you in finding the Control Ids of VBE Menu.
Open the VBE and insert a Module as shown below.
Once the module is inserted, it will look like this.
Copy the below code and paste it in the module. When you run this code, the ID’s will be printed to Sheet1.
'~~> 07th May 2006 # Siddharth Rout # '~~> The below code retrieves the control id's of the VBA Editor Private Sub GetMeIDs() Dim ws As Worksheet Dim Ctl As CommandBarControl Dim cCtl As CommandBarControl Dim i As Long, j As Long Set ws = ThisWorkbook.Sheets("Sheet1") On Error Resume Next i = 1: j = 1 '~~> Loop through the Top Level menus like File, Edit, View etc For Each Ctl In Application.VBE.CommandBars(1).Controls '~~> Write the ID and caption to Col A ws.Range("A" & i).Value = Ctl.ID & " - " & Ctl.Caption i = i + 1 '~~> Loop through Sub Level menus like File~~>New Project, File~~>Open Project etc For Each cCtl In Application.VBE.CommandBars(1).Controls(Ctl.Caption).Controls '~~> Write the ID and caption to Col B ws.Range("B" & j).Value = cCtl.ID & " - " & cCtl.Caption j = j + 1 Next i = j Next End Sub
This is how the ID’s will look
Create a Form and place a Listbox and a Command button on it.
Next set a reference to Microsoft Excel Object Library. Once done, simply use this code and run your project.
Imports Excel = Microsoft.Office.Interop.Excel Imports Microsoft.Office.Core Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click '~~> Define your Excel Objects Dim xlApp As New Excel.Application Dim Ctl As CommandBarControl Dim cCtl As CommandBarControl Dim sMainMenu As String = "" Dim sSubMenu As String = "" '~~> Hide Excel xlApp.Visible = False On Error Resume Next '~~> Loop through the Top Level menus like File, Edit, View etc For Each Ctl In xlApp.VBE.CommandBars(1).Controls sMainMenu = Ctl.Id & " - " & Ctl.Caption '~~> Loop through Sub Level menus like File~~>New Project, File~~>Open Project etc For Each cCtl In xlApp.VBE.CommandBars(1).Controls(Ctl.Caption).Controls sSubMenu = cCtl.Id & " - " & cCtl.Caption '~~> Write to listbox ListBox1.Items.Add(sMainMenu & "~~>" & sSubMenu) Next Next xlApp.Quit() '~~> Clean Up releaseObject(xlApp) End Sub '~~> Release the objects Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub End Class
When you click the button, you will see the Listbox populated with the IDs.
For Excel’s other commandbars you can use this simple Add-In by Add-In Express