Excel Add-in for a menu for a macro

[NightZEN]

I am trying to create and add-in that will add a menu item for users to click and call a macro called "HotList_Formating". I have the following code saved in an add-in file (hotlist_formater.xla) in it's "ThisWorkbook" Object. I did not include the called subroutines here to save space, but they are saved in the same location. When I add the add-in and click the menu button I get the following error:

"The macro 'hotlist_formater.xla!HotList_Formating' cannot be found."

Why Not? Please help me to understand where I'm going wrong. Thanks!!!!!

Option Explicit
Dim cControl As CommandBarButton

Private Sub Workbook_AddinInstall()

    On Error Resume Next 'Just in case
    'Delete any existing menu item that may have been left.
    Application.CommandBars("Worksheet Menu Bar").Controls("HotList Formater").Delete
    'Add the new menu item and Set a CommandBarButton Variable to it
    Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add
    'Work with the Variable
        With cControl
            .Caption = "HotList Formater"
            .Style = msoButtonCaption
            .OnAction = "'" & ThisWorkbook.Name & "'!HotList_Formating"
            'Macro stored in a Standard Module
        End With
        
    On Error GoTo 0
End Sub
Private Sub Workbook_AddinUninstall()
    
    On Error Resume Next 'In case it has already gone.
    Application.CommandBars("Worksheet Menu Bar").Controls("HotList Formater").Delete
    On Error GoTo 0
End Sub

Sub HotList_Formating()
'
' HotList_Formating Macro
' Changes a cell's color based on the workcenter.
'

'
   Call AddHeaderColor
   Call AddLines
   Call ChangeColor("M4:M2000")
   Call ChangeColor("J4:J2000")
   
End Sub

 

[rmikesmith]

Move procedure HotList_Formating to a standard code module. If you don't already have one in your project, select Insert|Module from the main menu in the VB Editor.


Regards,
Mike

 

[rmikesmith]

Alternatively, you can keep the HotList_Formating procedure where it is by qualifying the procedure name with ThisWorkbook:

.OnAction = "'" & ThisWorkbook.Name & "'!Thisworkbook.HotList_Formating"

I prefer to place most of my routines in standard code modules, however.


Regards,
Mike

 

[NightZEN]

That did it Mike, Thanks!

 

[firefytr]

Hi there,

I would recommend using a Class Module if you want your addin menu to be dyanmic. Check out the code generated here..

http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1135732427

-----------
Regards,
Zack Barresse

 

[rmikesmith]

Nice piece of work. Don't suppose you could incorporate some of your energy into an Add-In?


Regards,
Mike

 

[firefytr]

Thank you. And of course. I love making addnis.

-----------
Regards,
Zack Barresse