Sharing Add-in Problem

[tquist]

I am having trouble sharing an Excel Add-in I've created which contains three macros (modules). I have looked everywhere but, oddly, I can't seem to find a definitive answer. I would like for people to be able to install the add-in, and have access to three macros which it contains.

What is the best solution to this problem? I would like to add buttons to the add-in ribbon that execute each macro individually. The macros should be available to all workbooks once the add-in is installed.

Any help would be greatly appreciated!

Tom

 

[andrewk32]

I am having trouble sharing an Excel Add-in I've created which contains three macros (modules). I have looked everywhere but, oddly, I can't seem to find a definitive answer. I would like for people to be able to install the add-in, and have access to three macros which it contains.

What is the best solution to this problem? I would like to add buttons to the add-in ribbon that execute each macro individually. The macros should be available to all workbooks once the add-in is installed.

Any help would be greatly appreciated!

Tom

I had the same problem I found a good sample here
http://www.contextures.com/xlToolbar02.html

Let me know if you need any help modifying it

 

[tquist]

Thanks for your reply!

I found some code that will add in command buttons to the "Add-ins" ribbon. It still doesn't want to execute my macros though. Any thoughts on why this isn't working correctly?

Option Explicit

Private Sub Workbook_AddinInstall()
Dim PopulateTable As CommandBarButton
Dim CreateChart As CommandBarButton
Dim TableFrame As CommandBarButton
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Box Plot Utility").Delete
Set TableFrame = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With TableFrame
.Caption = "Create Table Outline"
.Style = msoButtonCaption
.OnAction = "TableFrame"
.TooltipText = "Creates a table pre-formatted to work with the Box Plot Utility.  Do NOT disturb the formatting beyond adding additional columns or errors will likely occur!"
End With
Set PopulateTable = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With PopulateTable
.Caption = "Populate Table"
.Style = msoButtonCaption
.OnAction = "TableFrame"
.TooltipText = "Calculates statistics and produces data necessary to create a box-and-whisker chart.  Make sure that the cell with the fiscal year label is selected before issuing this command!"
End With
Set CreateChart = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With CreateChart
.Caption = "Create Box Plot"
.Style = msoButtonCaption
.OnAction = "BoxPlotCreator"
.TooltipText = "Creates a box-and-whisker chart. Make sure that the top-left cell of the title bar is selected before issuing this command!"
End With
Set TableFrame = Nothing
Set PopulateTable = Nothing
Set CreateChart = Nothing
End Sub

Private Sub Workbook_AddinUninstall()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Box Plot Utility").Delete
End Sub

This code is added to the "Thisworkbook" part of my .xlam add-in which contains the three macro modules. Thanks again!

 

[andrewk32]

Not sure I tried changing the sub and assigning it to a button and it worked fine. For what ever reason the Addininstall Event is not firing

Here's some sample code I got from the microsoft help file

<code>Private Sub Workbook_[B]AddinInstall[/B]()     

With Application.Commandbars("Standard").Controls.Add         
     .Caption = "The AddIn's menu item"         
     .OnAction = "'ThisAddin.xls'!Amacro" 
End With 
End Sub</code>

But even that doesn't work

 

[andrewk32]

I figured out what was wrong. the "Worksheet Menu Bar" is the default bar in Excel 2003 (File, Edit,... ) I'm not sure if there is one in 2007. What you wan to do is create a whole new toolbar. I tried it out and it seems to work fine. I hope you don't mind I rewrote your code to match the example that I linked to earlier.(I was having trouble getting it to work to I tried the one that I knew would)

Private Sub Workbook_AddinInstall()

    Dim iCtr As Long

    Dim MacNames As Variant
    Dim CapNamess As Variant
    Dim TipText As Variant
    Dim Toolbarname As String
    
    Toolbarname = "Custom Toolbar"
    MacNames = Array("TableFrame", _
                     "PopulateTable", _
                        "BoxPlotCreator ")

    CapNamess = Array("Create Table Outline", _
                      "Populate Table", _
                      "Create Box Plot")

    TipText = Array("Creates a table pre-formatted to work with the Box Plot Utility.  Do NOT disturb the formatting beyond adding additional columns or errors will likely occur!", _
                    "Calculates statistics and produces data necessary to create a box-and-whisker chart.  Make sure that the cell with the fiscal year label is selected before issuing this command!", _
                    "Creates a box-and-whisker chart. Make sure that the top-left cell of the title bar is selected before issuing this command!")
    
    With Application.CommandBars.Add(Toolbarname)
        .Left = 200
        .Top = 200
        .Protection = msoBarNoProtection
        .Visible = True
        .Position = msoBarFloating



        For iCtr = LBound(MacNames) To UBound(MacNames)
           With .Controls.Add(Type:=msoControlButton)
                .OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
                .Caption = CapNamess(iCtr)
                .Style = msoButtonCaption
                .FaceId = 71 + iCtr
                .TooltipText = TipText(iCtr)
            End With
        Next iCtr
    End With
End Sub

Private Sub Workbook_AddinUninstall()
    Dim Toolbarname As String
    
  Toolbarname = "Custom Toolbar"
    On Error Resume Next
Application.CommandBars(Toolbarname).Delete
End Sub

 

[tquist]

Andrew,

Thanks for the code - that looked much cleaner than what I was using. Unfortunately, I'm still having the issue of new workbooks being unable to access the macros embedded within the add-in. If I import the modules into a new workbook, I can execute them - but I wouldn't want others to have to do that. I can't figure out why the reference isn't working! This is driving me nuts!

Thank you again for your help...

 

[andrewk32]

It sounds like your macros may reference the add-in workbook instead of the active workbook. Link the code for the macros and I could tell you where the error is.

 

[tquist]

Andrew,

I think I just resolved the issue - although it's a little confusing as to why it worked. When I reference the macro from the control button, I have to refer to it as follows:

MacNames = Array("TableFrame.TableFrame", _
                     "TablePopulator.TablePopulator", _
                        "BoxPlotCreator.BoxPlotCreator ")

As you can see, I have to write the name of the macro twice, separated by a period. Not sure why, but this seems to resolve the issue.

I really appreciate your help and if I run into further problems, I'll let you know.

Thanks again,

Tom

 

[andrewk32]

Sounds like you created it as a class instead of a module, but either way works.

 

[tquist]

Interesting...shows how little I know about this stuff! Hopefully I'll get more efficient as I roll along. Thanks again for your help!

Tom