Re: Menu Questions

From: Dick Kusleika (dickk_at_paragonUNMUNGEconstructioninc.com)
Date: 10/18/04 

Date: Mon, 18 Oct 2004 15:41:07 -0500

David

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

Application.CommandBars(1).Controls("MyCaption").Delete

If you want to "see" it, you could use a sub like

Dim ctl as CommandBarControl

For Each ctl In Application.CommandBars(1).Controls
    MsgBox ctl.Caption
Next ctl

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

With Application.CommandBars.Add(Name:="MyNewBar")
    etc... 

-- 
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" <schrader@acns.fsu.edu> 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)
>
>

Relevant Pages

  • Re: Row menu Insert control ID changes
    ... Sub TestFindId() ... What happens is that the Insert control on the ... To see this, add this module to a workbook, save it, close Excel and then ...
    (microsoft.public.excel.programming)
  • Re: VBA:programmatically invoking menu items from Excel Worksheet
    ... Sub ControlBarsInfo() ... 'potential properties of CommandBar objects: ... Dim anyCB As CommandBar ... Excel WorkSheet menu bar from within VBA subroutines and functions (do ...
    (microsoft.public.excel.programming)
  • Re: Row menu Insert control ID changes
    ... control changes IDs from 3181 right at startup to 3183. ... Not sure what's going on here; some playing with the menu by Excel ... Sub TestFindId() ... To see this, add this module to a workbook, save it, close Excel ...
    (microsoft.public.excel.programming)
  • Re: Row menu Insert control ID changes
    ... control changes IDs from 3181 right at startup to 3183. ... Not sure what's going on here; some playing with the menu by Excel ... Sub TestFindId() ... To see this, add this module to a workbook, save it, close Excel and ...
    (microsoft.public.excel.programming)
  • Re: Row menu Insert control ID changes
    ... Not sure what's going on here; some playing with the menu by Excel behind ... Sub TestFindId() ... This assumes that the Insert control is number 5 on the Row ... Professional Excel Development and Daily Dose of Excel) when a workbook ...
    (microsoft.public.excel.programming)