Late Binding for Excel CommandBar and CommandBarControl
 

Hello,

I have the following piece of code in an Excel .xla add-in:

---------------------------------------------------------------------------------------------------------------------------

Dim MenuBar As CommandBar
Dim MenuItem As CommandBarControl

Application.CommandBars("Worksheet Menu Bar").Controls("New
Menu").Delete
Set MenuBar = Application.CommandBars("Worksheet Menu Bar")
Set MenuItem = MenuBar.Controls.Add(Type:=msoControlPopup)
MenuItem.Caption = "New Menu"

---------------------------------------------------------------------------------------------------------------------------

This works fine when I have the following Reference checked:

Microsoft Office 11.0 Object Library

Since not all people who will be using this .xla will have this version
of the Object Library, I would like to change this from Early Binding
to Late Binding. Unchecking this Reference gives me numerous problems
when I try to run the program.

Can anyone tell me what the corresponding code would be for "Late
Binding"?

Thanks,
Sam.

Late Binding for Excel CommandBar and CommandBarControl
 

I must admit to having never come across this problem.

I don't see how you can get around it as you have to reference a library to
develop it.

My only thought is to develop in your user's lowest version if you can get
hold of a copy.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Hello,

I have the following piece of code in an Excel .xla add-in:

--------------------------------------------------------------------------
-------------------------------------------------

Dim MenuBar As CommandBar
Dim MenuItem As CommandBarControl

Application.CommandBars("Worksheet Menu Bar").Controls("New
Menu").Delete
Set MenuBar = Application.CommandBars("Worksheet Menu Bar")
Set MenuItem = MenuBar.Controls.Add(Type:=msoControlPopup)
MenuItem.Caption = "New Menu"

--------------------------------------------------------------------------
-------------------------------------------------

This works fine when I have the following Reference checked:

Microsoft Office 11.0 Object Library

Since not all people who will be using this .xla will have this version
of the Object Library, I would like to change this from Early Binding
to Late Binding. Unchecking this Reference gives me numerous problems
when I try to run the program.

Can anyone tell me what the corresponding code would be for "Late
Binding"?

Thanks,
Sam.

Late Binding for Excel CommandBar and CommandBarControl
 

Hi Sam,

In general Late Binding means declaring all object references that will use
a given library 'As Object' and when automating use CreateObject(some app)
rather than New some-app. Some other things like using constant values
rather than named constants from the library.

However I don't think this is the issue of your problem and converting to
Late binding won't resolve. Simply compile and save your xla in the lowest
version of Excel & Office of any of your anticipated users.

Regards,
Peter T


wrote in message
oups.com...
Hello,

I have the following piece of code in an Excel .xla add-in:

--------------------------------------------------------------------------
-------------------------------------------------

Dim MenuBar As CommandBar
Dim MenuItem As CommandBarControl

Application.CommandBars("Worksheet Menu Bar").Controls("New
Menu").Delete
Set MenuBar = Application.CommandBars("Worksheet Menu Bar")
Set MenuItem = MenuBar.Controls.Add(Type:=msoControlPopup)
MenuItem.Caption = "New Menu"

--------------------------------------------------------------------------
-------------------------------------------------

This works fine when I have the following Reference checked:

Microsoft Office 11.0 Object Library

Since not all people who will be using this .xla will have this version
of the Object Library, I would like to change this from Early Binding
to Late Binding. Unchecking this Reference gives me numerous problems
when I try to run the program.

Can anyone tell me what the corresponding code would be for "Late
Binding"?

Thanks,
Sam.