My Blog

Find Excel’s VBE Command Bar names and built-in controls IDs. – VBA and VB.Net

1

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.

VBA

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

VB.NET

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

  1. Eric
    Eric05-02-2013

    Thank you!

    ~ Eric