• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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???
0
themroc
Asked:
themroc
  • 3
  • 2
2 Solutions
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now