Turning a Macro into an add-in for Excel?

[John]

Hello.

Does anyone know how it is possible to turn a macro that is saved in
the personal macro workbook into an Excel add-in, so that it stays in
the Add-in section of the tools menu, and when checked adds the extra
buttons to the toolbar that run the macros automatically?

I seem to remember a while back being able to get the add-in part done
and have it in the add-in section of the tools menu, but could never
get it to also put the buttons in automatically that are used to run
the two macros.

I would like to be able to save this as an add-in on a remote drive
accessible to everyone, instead of having it on everyones individual
computers.

A lot of other add-ins I have seem to add buttons into the toolbar to
activate macros, but I could never get it to do that for the macros I
made.

Cheers very much for any help

John

 

[Bob Phillips]

John,

Here is a re-post of an earlier reply


In summary, you should create a temporary toolbar in the workbook_Open event
of your add-in. Then when Excel starts, as long as tyhe add-in is installed,
the toolbar will be available. If you also remove it the
workbook_BeforeClose event it will be removed immeditaely the add-in is
de-installed, but will not be ther next time Excel start5s as it is
temporary.


The add-in does not need to be in XLStart as it is installed, so Excel will
know where it is.


Here is some sample code to create a toolbar as suggested. This code would
go in the ThisWorkbok code module.


I would also add my usual corollary that to see what FaceIds are available,
visit John Walkenbach's site at http://j-walk.com/ss/excel/tips/tip67.htm


Option Explicit


Dim appMenu As String


Private Sub Workbook_BeforeClose(Cancel As Boolean)


appMenu = "My Toolbar"


On Error Resume Next
Application.CommandBars(appMenu).Delete
On Error GoTo 0


End Sub


Private Sub Workbook_Open()
Dim oCB As CommandBar


appMenu = "My Toolbar"


On Error Resume Next
Application.CommandBars(appMenu).Delete
On Error GoTo 0


Set oCB = Application.CommandBars.Add(Name:=appMenu, temporary:=True)


With oCB
With .Controls.Add(Type:=msoControlButton)
.Caption = appMenu & " Toolbar"
.Style = msoButtonCaption
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Open File"
.FaceId = 23
.Style = msoButtonIconAndCaption
.OnAction = "OpenFiles"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Sort Results"
.FaceId = 210
.Style = msoButtonIconAndCaption
.OnAction = "BCCCSort"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "New Player"
.FaceId = 316
.Style = msoButtonIconAndCaption
.OnAction = "NewEntry"
End With
With .Controls.Add(Type:=msoControlDropdown)
.BeginGroup = True
.Caption = "Delete"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Delete "
.Style = msoButtonCaption
.OnAction = "RemoveEntry "
.Parameter = "Toolbar"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "New Sheet"
.FaceId = 18
.Style = msoButtonIconAndCaption
.OnAction = "NewSheet"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "New Workbook"
.FaceId = 245
.Style = msoButtonIconAndCaption
.OnAction = "NewBook"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "About..."
.FaceId = 941
.Style = msoButtonIconAndCaption
.OnAction = "About"
End With
.Visible = True
.Position = msoBarTop
End With


End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)