I need a late binding commandbar solution

I need to create a menu item in the Worksheet menu bar that will work across Excel Office versions.  I've written a solution for win office 2000 and win xp, however, I need to use late binding to reduce my reliance on the Microsoft Office 9.0 and 10.0 object libraries to reduce my distribution package complexity.

Do you know how create a commandbar late binding solution?

Kathleen
krkerwinAsked:
Who is Participating?
 
GrahamSkanRetiredCommented:
yes Kathleen

As a generality, I would expect code written for one version to work with later versions, and I couldn't see the reason for two versions, given that you want to use late binding.

I have just cross-checked  both routines on the alternate versions. Both routines work on both Office versions.

Cheers, Graham
0
 
GrahamSkanRetiredCommented:
It is very simple to convert from early to late binding.

1. Declare all your objects as Object.
2. Use CreateObject instead of New

Like so
Sub CreateBar()
    Dim xlApp As Object 'Excel.Application
    Dim cb As Object 'Office.CommandBar

    Set xlApp = CreateObject("Excel.Application") 'New Excel.Application
    xlApp.Visible = True
    Set cb = xlApp.CommandBars.Add("Mybar")
    cb.Visible = True
End Sub
 
0
 
krkerwinAuthor Commented:
GrahamSkan:
Looks good at a high level.  I have difficulty getting the details of the late binding of the commandbar.  I use the existing worksheet menu bar and add a new entry then new items to the new entry in the menu bar.  I'm having difficulty translating the solutions below into late binding.  Can you translate them?

In Office XP:
Dim NewMenu As CommandBarPopup
Dim MenuItem As CommandBarControl
Dim Submenuitem As CommandBarButton

'   Add a caption for the menu
    NewMenu.Caption = "&New Bar Entry"
         
'   1 MENU ITEM
    Set MenuItem = NewMenu.Controls.Add _
      (Type:=msoControlButton)
   
    x = 1
    With MenuItem
        .Caption = "&New Bar Entry item1"
        .FaceId = 162
        .OnAction = "!ShowNewBarEntryitem1" '
    End With

In Office 2000:
Set myMnu = CommandBars.FindControl(Type:=msoControlPopup, ID:=30095)

   Set myMnu = CommandBars("Worksheet menu bar").Controls. _
      Add(Type:=msoControlPopup, Before:=7)
   With myMnu
   ' The "&" denotes a shortcut key assignment (Alt+M in this case).
      .Caption = "&New Bar Entry"
   End With
   
'   1 MENU ITEM'  .FaceId = 162
   With CommandBars("Worksheet menu bar").Controls("New Bar Entry")
      .Controls.Add(Type:=msoControlButton, Before:=1).Caption = "New Bar Entry Item 1"
      .Controls("New Bar Entry Item 1").OnAction = "ShowNewBarEntryItem1"
   End With

Kathleen
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GrahamSkanRetiredCommented:
Hi Kathleen,

I forgot about constants.

This works on Excel 2003 (I don't have XP)

Private Sub Command1_Click()
    Dim xlApp As Object
    Dim MenuItem As Object
    Dim SubmenuItem As Object
    Dim x As Integer
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set NewMenu = xlApp.CommandBars("Worksheet Menu Bar").Controls.Add(10) 'msoControlPopup
       
'   Add a caption for the menu
    NewMenu.Caption = "&New Bar Entry"
         
'   1 MENU ITEM
    Set MenuItem = NewMenu.Controls.Add(1)   ' msoControlButton
   
    x = 1
    With MenuItem
        .Caption = "&New Bar Entry item1"
        .FaceId = 162
        .OnAction = "!ShowNewBarEntryitem1" '
    End With
    MsgBox "OK"
    xlApp.quit
End Sub

I'll look at the 2000 version, now
Cheers, Graham
0
 
GrahamSkanRetiredCommented:
This works on Excel 2000

    Dim xlApp As Object 'Excel.Application
    Dim myMnu As Object 'CommandBarPopup
    Const msoControlPopup = 10
    Const msoControlButton = 1
   
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
   
    Set myMnu = xlApp.CommandBars("Worksheet menu bar").Controls. _
    Add(Type:=msoControlPopup, Before:=7)
    With myMnu
        ' The "&" denotes a shortcut key assignment (Alt+M in this case).
        .Caption = "&New Bar Entry"
    'End With
   
    '   1 MENU ITEM'  .FaceId = 162
    'With xlApp.CommandBars("Worksheet menu bar").Controls("New Bar Entry")
    .Controls.Add(Type:=msoControlButton, Before:=1).Caption = "New Bar Entry Item 1"
    .Controls("New Bar Entry Item 1").OnAction = "ShowNewBarEntryItem1"
    End With
    MsgBox "OK"
    xlApp.quit
0
 
krkerwinAuthor Commented:
GrahamSkan:
Very close.  The point of late binding is to eliminate the multiversion code entries.  I have two versions at this point.  I have my addin for the XP version referenced to the Microsoft Office 10.0 object library and another copy of the same addin for the Office 2000 references the Microsoft Office 9.0 object library.  The two code versions are in the single addin code that are triggered by the OS and Office version.  I need to eliminate the duel code versions and late bind a single version without referencing either the 9.0 or 10.0 libraries.

From the looks of your Office 2003 version, it may work on Office XP and Office 2000.  Both the Office XP and Office 2000 object browsers recognize the CommandBars without the 9.0 and 10.0 object libraries being referenced.  However, the msoControlPopup is not recognized by the unreferenced Office XP version.

I'll work on it.

Thank you for your feedback.  I've been looking a while for this solution.

Kathleen
0
 
krkerwinAuthor Commented:
Still working on the Commandbar solution you proposed.  

Of of curiousity, when you say both versions will work do you have both the 9.0 and 10.0 object library versions referenced in your test example?  When I delete both library references I definitely get an error when I use the msoControlPopup functionality on my Office XP computer.  The msoControlPopup doesn't exist in the object browser without the 9.0 object library referenced.

Just refining my understanding of referencing and late binding.

Kathleen
0
 
krkerwinAuthor Commented:
Ignore that last statement.  I just proved myself wrong regarding the reference error using your solution.

K
0
 
krkerwinAuthor Commented:
I've successfully added and deleted the new entry and new entry item.  Strangely enough though, the newly added to 'Worksheet menu bar' disappeared upon existing the createmenu function.  However, the entries were still there to be deleted.  Curious phenonomn.  Then I realized that I needed to use set xlAPP = GetObject(,"Excel.Application") to add to the current 'Worksheet menu bar' rather than create a new 'worksheet menu bar'

Works great.  Any idea of how to add the faceids?  

Kathleen
0
 
krkerwinAuthor Commented:
Figured the faceids out.

With xlApp.CommandBars("Worksheet menu bar").Controls("New Bar Entry")
    .Controls.Add(Type:=msoControlButton, Before:=1).Caption = "New Bar Entry Item 1"
    .Controls("New Bar Entry Item 1").onAction = "!ShowNewBarEntry1"
    .Controls("New Bar Entry Item 1").faceid = 162
end with

Nice work.  Thank you for you solution.
0
 
GrahamSkanRetiredCommented:
Well done Kathleen. Thanks for the grade.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.