Creating CommandBar Button in Excel 2003

Oct 18, 2012 at 2:32 AM

I'm attempting to create a button in an already existing commandbar menu but am having a heck of a time. I've been able to create one using late binding since I can't use NetOffice since it doesn't expose CommandBarControl.Add method. But I haven't been able to capture the event when I do it this way. Any help would be appreciated!

My late bound code:

 

Dim�oControl�AsObject�=�xlApp.CommandBars.FindControl(Type.Missing,�CiFilter_WorksheetMenuBarControlID)

Dim�oNewControl�=�oControl.Controls.Add(Type:=MsoControlType.msoControlButton,�Temporary:=True)


This is what I've used as my template:

http://www.cpearson.com/excel/CreatingCOMAddIn.aspx

Coordinator
Oct 23, 2012 at 8:48 AM

NetOffice doesnt expose the CommandBarControl.Add method ?
you got an exception or whats the problem and did you try the excel example09.vb ?

Oct 23, 2012 at 4:05 PM

That did it. I guess I was looking in the wrong place to figure out how to do it!

Oct 23, 2012 at 8:43 PM

OK, it didn't quite do it. What I'm trying to do is add a control under an existing control location (Filter) so this works in VBA:

   

Dim oControl As CommandBarControl
Set oControl = Application.CommandBars.FindControl(ID:=30031)
Dim oNewControl As CommandBarControl
Set oNewControl = oControl.Controls.Add(Type:=MsoControlType.msoControlButton, Temporary:=True)
With oNewControl
  .Caption = "&New Filter"
End With

But in NetOffice I cannot add a control from another control like I did above. I'll keep trying different things. :\

Coordinator
Oct 25, 2012 at 10:32 AM

i try to use your code in VBA/Excel2007 but the CommandBarControl has also no Controls Collection here. what is your excel version? Oo

Sebastian

Oct 25, 2012 at 2:21 PM

I'm using Excel 2003 on Windows XP.

Thanks for taking a look at this!

Coordinator
Oct 25, 2012 at 3:03 PM

this is magic. the office object library doesnt have the Controls property for CommandBarControl. the VBA intellisense doesnt show the Controls property but it works at runtime! its not clearly for why this code works and i need some days to clearify the situation. as hotfix i suggest you to create an extension to use the Controls property.

Imports NetOffice
Imports NetOffice.OfficeApi

Public Module CommandBarControlExtensions

    <System.Runtime.CompilerServices.Extension()>
    Public Function Controls(ByVal ctrl As CommandBarControl) As CommandBarControls

        Dim ctrls As Object = NetOffice.Invoker.PropertyGet(ctrl, "Controls")
        Return NetOffice.Factory.CreateObjectFromComProxy(ctrl, ctrls)

    End Function

End Module

Oct 25, 2012 at 11:22 PM

I tried your code:

    <Extension()>
    Public Function Controls(ByRef ctrl As CommandBarControl) As CommandBarControls

        Dim ctrls As Object = NetOffice.Invoker.PropertyGet(ctrl, "Controls")
        Return NetOffice.Factory.CreateObjectFromComProxy(ctrl, ctrls)

    End Function

But it didn't like the first line (Invoker).

I was thinking that it isn't absolutely necessary that I put the control in the "Filter" menu. I could just place it under it. So, if you have higher priority items don't worry about it, it is just a "nice to have" but not necessary.

Thanks for your help!

Coordinator
Oct 26, 2012 at 8:53 AM

no idea why it doesnt works for you. i create an example project.
please checkout: http://netoffice.codeplex.com/wikipage?title=Download%20Page

Sebastian

Oct 30, 2012 at 7:32 PM

That worked. Not sure why it wasn't working before. Thanks. Still not certain how to add the control in the correct place using this code. I'll keep working on it though.

Oct 30, 2012 at 8:31 PM

OK, it stops working when I use the .Add property with the Controls via the Extension.

oControl = _application.CommandBars _
   .FindControl(Type.Missing, 901)
Dim oNewerControl = oControl.Controls _
   .Add(MsoControlType.msoControlButton, Type.Missing, Type.Missing, oControl.Index, True)

The second line above.

Coordinator
Oct 31, 2012 at 6:35 PM

stop working means what? the best informations for me comes from the NetOffice debug console. but you can also copy the exception message and also the messages from all inner exceptions.
btw: add is not property ;)

Sebastian