Distributing macros by installing an Addin

[help_wanted]

I have created a workbook containing the macro that I need t
distribute... saved it as an .xla and installed it via Add-Ins. When
open a workbook I see the addin load but the macro is not available. I
is still looking for an open workbook containing the macro.... An
suggestions??? Is this only for user forms ??? Thanks for the tips o
add-ins and thanks in advance for any other info.

 

[Bob Phillips]

You need to install the add-in, not load it. Go to Tools>Add-ins, browse to
get the file, and select it, then check the entry.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harald Staff]

Hi

You must make it available through a menu or a toolbar. Paste this in the
addin's ThisWorkbook module and it'll make a new entry in Excel's Data menu:

Option Explicit '**** top of module

Const strMyTagId As String = "MyMacro1"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteCmb(strMyTagId)
End Sub

Private Sub Workbook_Open()
Dim cmbTools As CommandBarPopup
Dim cmbCustom As CommandBarButton
Call DeleteCmb(strMyTagId)
Set cmbTools = Application.CommandBars(1).FindControl(, 30011)
Set cmbCustom = cmbTools.Controls.Add(msoControlButton, Temporary:=True)
With cmbCustom
.Caption = "A very useful macro"
.Tag = strMyTagId
.OnAction = "'" & ThisWorkbook.Name & "'!Macro1"
.BeginGroup = True
End With
End Sub

Private Sub DeleteCmb(ByVal strTag As String)
On Error Resume Next
Dim cb As CommandBarControl
Dim cbars As CommandBars
Set cbars = Application.CommandBars
With cbars
Set cb = .FindControl(Tag:=strTag)
Do Until cb Is Nothing
cb.Delete
Set cb = .FindControl(Tag:=strTag)
Loop
End With
End Sub