My .xla custom menu items doesn't show up when using 3rd party software

[Michael Malinsky]

Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with Excel
(essentially document management software) that is causing problems.
If I open a workbook through that software, the menu that is supposed
to be created with my add-in does not appear even though the add-in is
checked in the Tools>Add-ins dialog. If a uncheck and add-in, close
the Add-in dialog, open the Add-in dialog, check my add-in, then close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office, but
this is a sticking point for which I cannot seem to find a solution.

Thanks,
Mike.

 

[smw226 via OfficeKB.com]

Hi Michael,

Try some simple code (like opening a message box) on the Auto_open code to
see if it is running correctly.

I remember we had the same problem and it was overcome by unloading and
loading the bar in VBA.

Thanks,

Simon

Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with Excel
(essentially document management software) that is causing problems.
If I open a workbook through that software, the menu that is supposed
to be created with my add-in does not appear even though the add-in is
checked in the Tools>Add-ins dialog. If a uncheck and add-in, close
the Add-in dialog, open the Add-in dialog, check my add-in, then close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office, but
this is a sticking point for which I cannot seem to find a solution.

Thanks,
Mike.

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200610/1

 

[Ron de Bruin]

hi Michael

If you run Excel from another program code in Add-ins will not run
(you see the add-ins checked in Tools>Add-ins)

Run this in Start > Run in Windows with Excel closed and you see it

excel.exe /s

 

[Michael Malinsky]

@smw226: I'll give that a try...thanks.

@Ron: Any workaround suggestions other than what was already
suggested?

Thanks.

 

[Peter T]

Dim xlApp As Application
Dim mwb As Workbook

Sub testStart()
Dim sName As String
sName = "C:\path\myAddin.xla"
Set xlApp = New Excel.Application
Set mwb = xlApp.Workbooks.Open(sName)
mwb.RunAutoMacros xlAutoOpen
xlApp.Workbooks.Add
xlApp.Visible = True

End Sub

Sub testQuit()
On Error Resume Next
mwb.RunAutoMacros xlAutoClose
Set mwb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

If using Late Binding change xlAutoOpen & xlAutoClose to their respective
constant values of 1 and 2

Regards,
Peter T

 

[Ron de Bruin]

If you create the code you can use this for example to open Excel with add-ins
from another program.

But you use a 3rd party application.


This was suggested by KeepItCool and should work:

Code from KeepItCool (Oct 21, 2004)

Sub LoadXLwithAddins()
Dim xl As Object
Dim ai As Object

Set xl = CreateObject("Excel.Application")

For Each ai In Application.AddIns
If ai.Installed Then
xl.Workbooks.Open(ai.FullName).RunAutoMacros 1
End If
Next

xl.Visible = True
Set xl = Nothing
End Sub

 

[Michael Malinsky]

Well I couldn't get either of the above two ideas to work. Maybe I
don't understand placement of the code. Peter T.'s code, at least how
I used it, caused Excel to lock up.

Ron, your code generated an error at the line:

xl.Workbooks.Open(ai.FullName).RunAutoMacros 1

The error was "Unable to get the Open Property of the Workbooks class."

Maybe this has to do with where I put the code, which was in the
Auto_Open of the add-in.

What am I missing?

Thanks.

 

[Peter T]

When you create a new automated instance of Excel installed Addins don't
load, also if you add (ie load) any workbooks to the automated instance
their 'auto macros' don't automatically run.

Both posted routines have in common
- create a new Excel instance
- load one or more addins and use RunAutoMacros to run the Auto_Open routine

The routines differ in the way the new instance was created, mine used early
binding in as much as it was designed for testing in an already running
instance of Excel, with a module level reference attached to the instance.

Dim xlApp As Application
though would be clearer to have declared as Excel.Application
Normally the instance should be closed using the testClose routine in
particular to destroy the object ref 'xlApp'. You say Excel locked up 'the
way you used it' but you didn't say how.

Ron's / KeepitCool's would be late binding if used outside Excel.

My routine only attempted to open only your own addin (none of any other
installed addins). Providing you managed to start the new instance and
supply the correct path to your addin I don't know why that should fail
(other than errant code in the auto open routine of your addin).

Ron's / KeepitCool's attempts to open all installed addins and on the same
line run their auto open's. Possible reasons for the error you got
- an installed addin is an Excel bundled xll, eg Analys32.xll
- sometimes the bundled xla's don't return the full path with fullname

If you want to load all installed addins, and assuming your own addin is
installed, either force through under
On Error Resume Next
or check the addin's extentension is "xla" and verify .Name <> .Fullname

For your purposes start your instance however and from where ever you intend
to, then decide if you want to open only your own addin or all addins, if
the latter handle possible errors as described above.

Regards,
Peter T

 

[Peter T]

From Auto_Open should add a MyMacro button to the Tools menu and optionally
delete any old MyMacro that wasn't deleted in a previous Auto_Close (if
indeed you want to delete menu on close). It also adds a version id to the
button's tag property.

Sub Auto_Open()
MyMenu True, 123
End Sub

Sub Auto_Close()
MyMenu False
End Sub

Sub MyMenu(bCreate As Boolean, Optional ver)
Dim cbcTools As CommandBarControl
Dim cbcNew As CommandBarControl

On Error Resume Next
'set a ref to Tools
Set cbcTools = _
Application.CommandBars("Worksheet Menu Bar").FindControl(Id:=30007)

'delete any/all MyMacro buttons
Do
cbcTools.Controls("MyMacro").Delete
Loop Until Err.Number

On Error GoTo 0

If bCreate Then
' you might not want temporary:=True
Set cbcNew = cbcTools.Controls.Add(Type:=1, temporary:=True)
cbcNew.Caption = "&MyMacro"
cbcNew.OnAction = "MyMacro"
cbcNew.Tag = "MyVer" & ver
End If

End Sub

Sub MyMacro()

MsgBox "MyMacro"
End Sub

Following to find & delete any menus with tag MyVer123 and replace with same
(only for testing of course)

Sub CheckMenu()
Dim bFoundCtrl As Boolean
Dim sTag As String
Dim cbc As CommandBarControl

sTag = "MyVer" & 123
On Error Resume Next
Do
Set cbc = Application.CommandBars.FindControl(Tag:=sTag)
If Not cbc Is Nothing Then bFoundCtrl = True
cbc.Delete
Loop Until Err.Number

If bFoundCtrl Then
MsgBox sTag & " found, will now re-create"
MyMenu True, 123
Else
MsgBox sTag & " not found, will now create new"
MyMenu True, True, 123
End If

End Sub

Regards,
Peter T

 

[Michael Malinsky]

Thanks, Peter. After monkeying around with it in my existing add-in
with no success, I copied your code exactly as posted into a new xla
and got it to work. Now I just need to modify the code to make a new
menu item and populate it with menu items and cut and paste some code
from my original add-in.

Thanks for the help!

 

[Peter T]

Hi Michael,

Er, looks like I posted a follow-up about creating menus intended for a
totally different thread after my comments about automated instances of
Excel & RunAutoMacros. I take it you are responding to that and not the
accidentally posted menu stuff. Either way glad one or other of those posts
has worked for you!

Guess I'd better re-post the menu stuff where it belongs (subject: Add-In
Keyboard Shortcut).

Regards,
Peter T

Thanks, Peter. After monkeying around with it in my existing add-in
with no success, I copied your code exactly as posted into a new xla
and got it to work. Now I just need to modify the code to make a new
menu item and populate it with menu items and cut and paste some code
from my original add-in.

Thanks for the help!

From Auto_Open should add a MyMacro button to the Tools menu and optionally
delete any old MyMacro that wasn't deleted in a previous Auto_Close (if
indeed you want to delete menu on close). It also adds a version id to the
button's tag property.

Sub Auto_Open()
MyMenu True, 123
End Sub

Sub Auto_Close()
MyMenu False
End Sub

Sub MyMenu(bCreate As Boolean, Optional ver)
Dim cbcTools As CommandBarControl
Dim cbcNew As CommandBarControl

On Error Resume Next
'set a ref to Tools
Set cbcTools = _
Application.CommandBars("Worksheet Menu Bar").FindControl(Id:=30007)

'delete any/all MyMacro buttons
Do
cbcTools.Controls("MyMacro").Delete
Loop Until Err.Number

On Error GoTo 0

If bCreate Then
' you might not want temporary:=True
Set cbcNew = cbcTools.Controls.Add(Type:=1, temporary:=True)
cbcNew.Caption = "&MyMacro"
cbcNew.OnAction = "MyMacro"
cbcNew.Tag = "MyVer" & ver
End If

End Sub

Sub MyMacro()

MsgBox "MyMacro"
End Sub

Following to find & delete any menus with tag MyVer123 and replace with same
(only for testing of course)

Sub CheckMenu()
Dim bFoundCtrl As Boolean
Dim sTag As String
Dim cbc As CommandBarControl

sTag = "MyVer" & 123
On Error Resume Next
Do
Set cbc = Application.CommandBars.FindControl(Tag:=sTag)
If Not cbc Is Nothing Then bFoundCtrl = True
cbc.Delete
Loop Until Err.Number

If bFoundCtrl Then
MsgBox sTag & " found, will now re-create"
MyMenu True, 123
Else
MsgBox sTag & " not found, will now create new"
MyMenu True, True, 123
End If

End Sub

Regards,
Peter T

When you create a new automated instance of Excel installed Addins don't
load, also if you add (ie load) any workbooks to the automated instance
their 'auto macros' don't automatically run.

Both posted routines have in common
- create a new Excel instance
- load one or more addins and use RunAutoMacros to run the Auto_Open routine

The routines differ in the way the new instance was created, mine used early
binding in as much as it was designed for testing in an already running
instance of Excel, with a module level reference attached to the instance.

Dim xlApp As Application
though would be clearer to have declared as Excel.Application
Normally the instance should be closed using the testClose routine in
particular to destroy the object ref 'xlApp'. You say Excel locked up 'the
way you used it' but you didn't say how.

Ron's / KeepitCool's would be late binding if used outside Excel.

My routine only attempted to open only your own addin (none of any other
installed addins). Providing you managed to start the new instance and
supply the correct path to your addin I don't know why that should fail
(other than errant code in the auto open routine of your addin).

Ron's / KeepitCool's attempts to open all installed addins and on the same
line run their auto open's. Possible reasons for the error you got
- an installed addin is an Excel bundled xll, eg Analys32.xll
- sometimes the bundled xla's don't return the full path with fullname

If you want to load all installed addins, and assuming your own addin is
installed, either force through under
On Error Resume Next
or check the addin's extentension is "xla" and verify .Name <> ..Fullname

For your purposes start your instance however and from where ever you intend
to, then decide if you want to open only your own addin or all addins, if
the latter handle possible errors as described above.

Regards,
Peter T

Well I couldn't get either of the above two ideas to work. Maybe I
don't understand placement of the code. Peter T.'s code, at least how
I used it, caused Excel to lock up.

Ron, your code generated an error at the line:

xl.Workbooks.Open(ai.FullName).RunAutoMacros 1

The error was "Unable to get the Open Property of the Workbooks class."

Maybe this has to do with where I put the code, which was in the
Auto_Open of the add-in.

What am I missing?

Thanks.

If you create the code you can use this for example to open Excel with
add-ins
from another program.

But you use a 3rd party application.

This was suggested by KeepItCool and should work:

Code from KeepItCool (Oct 21, 2004)

Sub LoadXLwithAddins()
Dim xl As Object
Dim ai As Object

Set xl = CreateObject("Excel.Application")

For Each ai In Application.AddIns
If ai.Installed Then
xl.Workbooks.Open(ai.FullName).RunAutoMacros 1
End If
Next

xl.Visible = True
Set xl = Nothing
End Sub

--
Regards Ron de Bruinhttp://www.rondebruin.nl

message@smw226: I'll give that a try...thanks.

@Ron: Any workaround suggestions other than what was already
suggested?

Thanks.