How to invoke Subroutines/Macros from an xla file in an Excel sheet

I I created an add in file (xla), which is always opened when I run Excel.
Here I have got different functions which are available for all worksheets open.

Now I recorded a marco and i put the result also in the xla file.
I declared the subroutine public

When I now go to
/Tools/ Macro / Macro
I do not find this Macro Name in the list, even i choose look in all open workbook.
What do I have to do to acces a macro in a xla file???
themrocAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
You can run it by typing the macro name into the dialog, but usually routines in an add-in need a menu (button on a toolbar) to run them. They do not show up in the macros list.
0
themrocAuthor Commented:
How do i create a menu button or perhaps better a list entry/
0
Patrick MatthewsCommented:
To create custom menu structures, I often use a modified version of the general approach Excel MVP John Walkenbach describes here:

http://spreadsheetpage.com/index.php/tip/creating_custom_menus/

It works in Excel 2007, but in a slightly funny way :)
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Rory ArchibaldCommented:
What did you mean by "perhaps better a list entry"?
0
themrocAuthor Commented:
I have seen add in files when ticked they create an entry with a specific name under tools in the excel menue.
0
Rory ArchibaldCommented:
You can certainly do that - it's just a variation of the technique J_Walk demonstrates in Patrick's link. As a rough example of how to create a new menu item under tools:

' In a normal module:
Option Explicit
' this is the name of the toolbar - you can leave this as is or change it
Const mcstrTOOLBAR_NAME As String = "Tools"
' this is the button caption - change as required
Const mcstrBUTTON_CAPTION As String = "Click me"
' this is the name of the macro that the button will run - change as needed
Const mcstrBUTTON_MACRO As String = "ClickHyperlink"

Sub AddMenu()
    Dim cbr As CommandBar, ctl As CommandBarControl, ctlParent As CommandBarControl
    ' worksheet menu bar
    Set cbr = Application.CommandBars(1)
    Set ctlParent = cbr.Controls(mcstrTOOLBAR_NAME)
    On Error Resume Next
    ctlParent.Controls(mcstrBUTTON_CAPTION).Delete
    On Error GoTo err_handle
    ' add button to it
    Set ctl = ctlParent.Controls.Add(msoControlButton, , , , True)
    With ctl
        .Caption = mcstrBUTTON_CAPTION
        .OnAction = mcstrBUTTON_MACRO
        .Style = msoButtonCaption
    End With
    
    Exit Sub
    
err_handle:
    MsgBox Err.Description
End Sub
Sub RemoveMenu()
    On Error Resume Next
    Application.CommandBars(1).Controls(mcstrTOOLBAR_NAME).Controls(mcstrBUTTON_CAPTION).Delete
End Sub


'In the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveMenu
End Sub


Private Sub Workbook_Open()
    AddMenu
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.