List FindControl ID NUmbers

[JugglerJAF]

I was recently given the following code to disable the Share Workbook menu:

Sub DisableShareWorkbookButton() 
Application.CommandBars.FindControl(ID:=2040).Enabled = False 
End Sub

Probably a massively newbie question but how do I list/find out the ID numbers of other menu items (either top level, eg File. Main menu level, eg Print Area. Or sub-menu, eg Clear Print Area).

I have a number of menu items in a particular spreadsheet that I need to disable to stop certain individuals from "breaking" the spreadsheet. Simply protecting the sheet doesn't work for everything (users can still clear or reset a print area).


JugglerJAF

 

[Greg Truby]

This is pretty much a straight copy from Walkenbach's code in Excel 2002 Power Programming with VBA pages 689-690, with lines added to show the ID numbers.

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> ListMenuInfo()
    <SPAN style="color:#00007F">Dim</SPAN> row <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Menu <SPAN style="color:#00007F">As</SPAN> CommandBarControl
    <SPAN style="color:#00007F">Dim</SPAN> MenuItem <SPAN style="color:#00007F">As</SPAN> CommandBarControl
    <SPAN style="color:#00007F">Dim</SPAN> SubMenuItem <SPAN style="color:#00007F">As</SPAN> CommandBarControl
    row = 1
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Menu <SPAN style="color:#00007F">In</SPAN> CommandBars(1).Controls
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> MenuItem <SPAN style="color:#00007F">In</SPAN> Menu.Controls
            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> SubMenuItem <SPAN style="color:#00007F">In</SPAN> MenuItem.Controls
                Cells(row, 1) = Menu.Caption
                Cells(row, 2) = Menu.ID
                Cells(row, 3) = MenuItem.Caption
                Cells(row, 4) = MenuItem.ID
                Cells(row, 5) = SubMenuItem.Caption
                Cells(row, 6) = SubMenuItem.ID
                row = row + 1
            <SPAN style="color:#00007F">Next</SPAN> SubMenuItem
        <SPAN style="color:#00007F">Next</SPAN> MenuItem
    <SPAN style="color:#00007F">Next</SPAN> Menu
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

HTH

 

[twilsonco]

Trying to resurrect a 10 year old thread, I know, but you don't by chance have equally awesome code for getting id's of everything on the ribbon?
Working with newer versions nowadays...******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>

 

[MPW]

Working through different issue but if you still need the info, check out this post:
http://www.mrexcel.com/forum/excel-questions/799238-foreign-language-issues-commands.html

The 5th post should give you what you were asking for.