Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

I need a late binding commandbar solution

Posted on 2005-04-30
11
Medium Priority
?
807 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:krkerwin
  • 6
  • 5
11 Comments
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 13903599
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
 

Author Comment

by:krkerwin
ID: 13903745
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
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 13903831
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 77

Expert Comment

by:GrahamSkan
ID: 13903864
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
 

Author Comment

by:krkerwin
ID: 13903951
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
 
LVL 77

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 13903994
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
 

Author Comment

by:krkerwin
ID: 13905411
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
 

Author Comment

by:krkerwin
ID: 13905430
Ignore that last statement.  I just proved myself wrong regarding the reference error using your solution.

K
0
 

Author Comment

by:krkerwin
ID: 13905567
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
 

Author Comment

by:krkerwin
ID: 13905585
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
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 13907149
Well done Kathleen. Thanks for the grade.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question