needing xla assistance

[JohnE]

I have an xla addin and want to have it run from a custom menu button. I
have the xla ready to go but am uncertain of how to get it to work using the
button. There are other xla addins that run from other buttons done by prior
person but no documentation on what they did and I see no other coding other
then the vba code for the addin. I thought it was a macro button but that is
not connecting. Can someone help out on this process?
Thanks.
.... John

 

[Jim Cone]

....John,
If you are going to distribute the add-in to others then you will need
to create the menu item(s) when the add-in is installed and delete then
when the add-in is uninstalled.
This is normally accomplished in the ThisWorkbook module using the
Private Sub Workbook_AddinInstall() and Private Sub Workbook_AddinUninstall()
events. You will have to write menu code for each of the subs.

If you are using the add-in only for yourself, then you can right-click a menu bar
and choose "Customize". Then right-click the custom menu item and
choose "Assign Macro". Enter the add-in name/ macro name in a format
similar to the following... 'MyAddinName.xla'!MyMacroName
(note the single quote marks and the exclamation point)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"JohnE"
wrote in message
I have an xla addin and want to have it run from a custom menu button. I
have the xla ready to go but am uncertain of how to get it to work using the
button. There are other xla addins that run from other buttons done by prior
person but no documentation on what they did and I see no other coding other
then the vba code for the addin. I thought it was a macro button but that is
not connecting. Can someone help out on this process?
Thanks.
.... John

 

[JohnE]

Jim... thanks for the info. Do you know of a example of this that is
available for viewing?
.... John

 

[Jim Cone]

This following code works as is.
Also, Debra Dalgeish and Dave Peterson have a slightly different
approach here... http://www.contextures.on.ca/xlToolbar02.html
'---
'In a standard module...
Sub AddMyButton()
RemoveMyButton 'Insurance
Dim MyMenuButton As CommandBarButton
With Application.CommandBars.FindControl(ID:=30011).Controls 'Data menu
Set MyMenuButton = .Add(msoControlButton)
End With
MyMenuButton.FaceId = 123
MyMenuButton.Caption = "MyCaption"
MyMenuButton.OnAction = ThisWorkbook.Name & "!MyMacroName"
Set MyMenuButton = Nothing
End Sub
'------
Sub RemoveMyButton()
On Error Resume Next
Application.CommandBars.FindControl(ID:=30011).Controls("MyCaption").Delete
End Sub
'--

'In the ThisWorkbook module...
Private Sub Workbook_AddinInstall()
On Error Resume Next
AddMyButton
End Sub

Private Sub Workbook_AddinUninstall()
RemoveMyButton
End Sub
'---

'In a standard module...
Sub MyMacroName()
MsgBox "you clicked me"
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"JohnE"
wrote in message
Jim... thanks for the info.
Do you know of a example of this that is available for viewing?
.... John

 

[JohnE]

Jim, got it working. Thanks.
.... John

This following code works as is.
Also, Debra Dalgeish and Dave Peterson have a slightly different
approach here... http://www.contextures.on.ca/xlToolbar02.html
'---
'In a standard module...
Sub AddMyButton()
RemoveMyButton 'Insurance
Dim MyMenuButton As CommandBarButton
With Application.CommandBars.FindControl(ID:=30011).Controls 'Data menu
Set MyMenuButton = .Add(msoControlButton)
End With
MyMenuButton.FaceId = 123
MyMenuButton.Caption = "MyCaption"
MyMenuButton.OnAction = ThisWorkbook.Name & "!MyMacroName"
Set MyMenuButton = Nothing
End Sub
'------
Sub RemoveMyButton()
On Error Resume Next
Application.CommandBars.FindControl(ID:=30011).Controls("MyCaption").Delete
End Sub
'--

'In the ThisWorkbook module...
Private Sub Workbook_AddinInstall()
On Error Resume Next
AddMyButton
End Sub

Private Sub Workbook_AddinUninstall()
RemoveMyButton
End Sub
'---

'In a standard module...
Sub MyMacroName()
MsgBox "you clicked me"
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"JohnE"
wrote in message
Jim... thanks for the info.
Do you know of a example of this that is available for viewing?
.... John