Add-In - OnAction Can't Find Macro & Subs Doesn't Affect Current Wkb

[alexbarham]

I am working on and add-in that deletes empty rows and columns for
sorting and subtotalling. The data is generated from a Crystal Report.


I manually run the code to generate the menu item and then am hoping
that the procedure will execute on any new sheet.

I am having two problems with the Add-In
A) When I click on the menu item, I get an error that the macro can't
be found - "The macro 'ClearSheet.xla!CleanSheet' cannot be found"
B) Executing the the code in the Add-In doesn't seem to delete the rows
and columns in the new workbook. I have to copy the code over to the
new workbook and then run the code from there.

Here is the code:

Public Sub CreateMenu()
Dim HelpMenu As CommandBarControl
Dim NewMenu As CommandBarPopup
Dim MenuItem As CommandBarControl
Dim SubMenuItem As CommandBarButton

Call DeleteMenu

Set HelpMenu = Application.CommandBars(1).FindControl(ID:=30010)

'If Help Menu doesn't exist add to end of menu items
If HelpMenu Is Nothing Then
Set NewMenu =
Application.CommandBars(1).Controls.Add(Type:=msoControlPopup,
temporary:=False)
Else
Set NewMenu =
Application.CommandBars(1).Controls.Add(Type:=msoControlPopup,
Before:=HelpMenu.Index, temporary:=False)
End If

NewMenu.Caption = "Reporting"

'First Menu Item
Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)

With MenuItem
..Caption = "Gross &Margin by Part ID"
..OnAction = "CleanSheet"
End With

End Sub

Sub DeleteMenu()
On Error Resume Next
CommandBars(1).Controls("Reporting").Delete
End Sub

Public Sub CleanSheet()
Deleted for brevity
(Code that deletes empty rows and columns)
End Sub

 

[Harald Staff]

Try

..OnAction = ThisWorkbook.Name & "!CleanSheet"

HTH. Best wishes Harald

 

[alexbarham]

I tried .OnAction = ThisWorkbook.Name & "!CleanSheet"
and I received the error message "The macro 'ClearSheet.xla!CleanSheet'
cannot be found".

 

[Jim Thomlinson]

Just to confirm... CleanSheet is spelled correctly, and it is a public sub
procedure coded in a module (not a sheet).

 

[alexbarham]

Yes. CleanSheet is spelled correctly, I checked several times as I've
racked my brain on several occasions over something that turned out to
be spelling. As for how the module was developed, I created it in a
Workbook and I saved the workbook as an xla file. I didn't actually do
it in a module. I did try creating the menu and the procedure in a
workbook and used .OnAction = "ThisWorkbook.CleanSheet" And it worked.

Should I have originally coded this in a module?

 

[Tushar Mehta]

Yes. CleanSheet is spelled correctly, I checked several times as I've
racked my brain on several occasions over something that turned out to
be spelling. As for how the module was developed, I created it in a
Workbook and I saved the workbook as an xla file. I didn't actually do
it in a module. I did try creating the menu and the procedure in a
workbook and used .OnAction = "ThisWorkbook.CleanSheet" And it worked.

Should I have originally coded this in a module?

Yes, put the code in a standard module.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[alexbarham]

Thank you so much to everyone. I knew it was something simple. I put
the code into a module, saved the workbook as a new xla file and now
the menu item finds the macro and the macro affects the current
workbook.