Re: Menu Questions
From: Dick Kusleika (dickk_at_paragonUNMUNGEconstructioninc.com)
Date: Mon, 18 Oct 2004 15:41:07 -0500
In Registrar, you're creating a CommandBarControl, not a CommandBar. You're
putting a control on Excel's Worksheet Menu Bar, just to the left of the
Help control, I imagine. Cycling through the CommandBar collection won't
reveal it to you because it's a control on an existing CommandBar.
To delete it, you can use
If you want to "see" it, you could use a sub like
Dim ctl as CommandBarControl
For Each ctl In Application.CommandBars(1).Controls
The twelfth message box should show you your control.
If you want to make your own toolbar instead of putting a control on an
existing one (like the Worksheet Menu Bar), then instead of With
Application.CommandBars(1) you would use
-- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "David F. Schrader" <firstname.lastname@example.org> wrote in message news:ulkerBUtEHA.3188@TK2MSFTNGP15.phx.gbl... > Dick, > > So I would have thought, but I'm getting some weird results. > Here is the exact code I used, Two "macros" which I then > expanded into three because I couldn't believe what I was > (really wasn't) seeing. (You'll note that the first part is just > the code you sent me inside of a "procedure" block.) > > Stick it in an Excel block. Then first run the "Register," then > the "UnRegister." After that try the "UnRegisterAll." Strange > results. > > I'd still appreciate any input. > > David > > *** --- Code Starts --- *** > Public Sub Registrar() > With Application.CommandBars(1) 'This is Excel's menu > With .Controls.Add(msoControlPopup, , , 13) 'Add a new menu item > .Caption = "&MyCaption" 'Caption for the new menu item > With .Controls.Add(msoControlButton) 'a bunch of With block > creating controls on the new menu > .Caption = "My&FirstItem" > .OnAction = "FirstMacro" > End With > With .Controls.Add(msoControlButton) > .Caption = "My&SecondItem" > .OnAction = "SecondMacro" > End With > 'etc for each control on your menu > End With > End With > End Sub ' Registrar > Public Sub UnRegistrar() > For Each bar In Application.CommandBars > If Not bar.BuiltIn Then > MsgBox "Bar name : " & bar.Name > Else > ' MsgBox "Bar name : " & bar.Name > End If > Next > End Sub ' UnRegistrar > Public Sub UnRegistrarAll() > For Each bar In Application.CommandBars > MsgBox "Bar name : " & bar.Name > Next > End Sub 'UnRegistrarAll > > > *** --- Code Ends --- *** > > (snip) > >