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. Advertising

  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)


    "John" <> wrote in message
    news:...
    > 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, Dec 8, 2004
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. fourstriper
    Replies:
    2
    Views:
    727
    °Mike°
    Feb 5, 2004
  2. fredcromer
    Replies:
    1
    Views:
    627
    °Mike°
    Apr 21, 2004
  3. Dan Jacobson

    turning traditional cameras into digital cameras

    Dan Jacobson, Oct 30, 2004, in forum: Digital Photography
    Replies:
    9
    Views:
    355
    Phil Wheeler
    Oct 31, 2004
  4. Beauchampy

    Turning a drawing into a logo

    Beauchampy, Apr 8, 2005, in forum: Digital Photography
    Replies:
    3
    Views:
    2,257
    Stewy
    Apr 11, 2005
  5. Giuen
    Replies:
    0
    Views:
    982
    Giuen
    Sep 12, 2008
Loading...

Share This Page