Turning a Macro into an add-in for Excel?

Discussion in 'Computer Information' started by John, Dec 8, 2004.

  1. John

    John Guest

    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
     
    John, Dec 8, 2004
    #1
    1. Advertisements

  2. John

    Bob Phillips Guest

    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)
     
    Bob Phillips, Dec 8, 2004
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.