Cannot Run Macro Created with Add-in in Excel

Question

I have create an xla (excel add-in) that have a function to protect the document (so that user could protect the document without knowing the password). This xla is added in every excel file that need this functionality.

when the xla is installed or added to excel, the protect button will be added in last menu. but when i click the button, an error occur show that

"Cannot run the macro Pivot Add-In 0.2'!protectSheet'". The macro may not be available in this workbook or all macros may be disabled."

The code that event handler onclicked is protectSheet, please see the source below:

Could anyone pointed my why this problem occur?

on ThisWorkbook

'main function'
Public Sub protectSheet()
    ActiveWorkbook.ActiveSheet.protect Password:="password", AllowUsingPivotTables:=True
End Sub

Public Sub ShowToolbar()
' Assumes toolbar not already loaded '
Application.CommandBars.Add Module1.TOOLBARNAME
AddButton "Proteksi Sheet", "Memproteksi Pivot", 3817, "protectSheet"

' call AddButton more times for more buttons '
With Application.CommandBars(Module1.TOOLBARNAME)
    .Visible = True
    .Position = msoBarTop
End With
End Sub

Public Sub AddButton(caption As String, tooltip As String, faceId As Long, methodName As String)
Dim Btn As CommandBarButton
Set Btn = Application.CommandBars(Module1.TOOLBARNAME).Controls.Add
With Btn
    .Style = msoButtonIcon
    .faceId = faceId ' choose from a world of possible images in Excel: see     http://www.ozgrid.com/forum/showthread.php?t=39992 '
    .OnAction = methodName
    .TooltipText = tooltip
End With
End Sub

Public Sub DeleteCommandBar()
    Application.CommandBars(TOOLBARNAME).Delete
End Sub



'called when add-in installed
Private Sub Workbook_AddinInstall()
    Call ShowToolbar
End Sub

'called when add-in uninstalled
Private Sub Workbook_AddinUninstall()
    Call DeleteCommandBar 
End Sub

On module1

Public Const TOOLBARNAME = "PivotTools"

If appropriate, you should mark some of your previous questions as answered. Have you looked at: support.microsoft.com/kb/930076 and macros are enabled? — Reafidy
In my excel, the macro is enabled (by choose radio button "Enable all macro") — indrap
I cannot reproduce this behaviour: 1) I don't find any code that would automatically load the toolbar when the XLA is loaded (like Auto_Open) .... 2) have you saved your XLA on the basis of a completely empty sheet before adding code? The code runs well here if I manually start the toolbar — MikeD
@MikeD: (1) Yes, this is only part, please see the updated script (script to add the button when add-in is installed) (2) yes, the sheet is completely empty. It work when attached the menu manually but it not work when using programatically ways. — indrap
which version of Excel ? Macro security has changed since 2003... — Patrick Honorez

indrap indrap · Accepted Answer · 2011-09-14T07:34:17

After moving all function to module1 , then retain caller function on ThisWorkbook the error now gone. Seem that i have to define all functionality that call/ use constant (Public Const TOOLBARNAME = "PivotTools") in the same file (in my case in module1)

on module1 file

Public Const TOOLBARNAME = "PivotTools"

'caller method'
Public Sub protectDoc()


On Error GoTo errorInfo
    protectSheet

'if success, show msg box'
MsgBox ("Report berhasil diproteksi")

Exit Sub

errorInfo:
    MsgBox Err.Description & vbCrLf & Err.Number

End Sub


Public Sub protectSheet()
    ActiveWorkbook.ActiveSheet.protect Password:="password", AllowUsingPivotTables:=True
End Sub

Public Sub refreshDoc()

On Error GoTo errorInfo

refreshConnection
protectSheet
'if success, show msg box'
MsgBox ("Report berhasil diperbaharui")

Exit Sub

errorInfo:
    MsgBox Err.Description & vbCrLf & Err.Number

End Sub


Private Sub refreshConnection()
    ActiveWorkbook.ActiveSheet.Unprotect Password:="password"
    ActiveWorkbook.RefreshAll
End Sub


Public Sub ShowToolbar()
    ' Assumes toolbar not already loaded '
    Application.CommandBars.Add TOOLBARNAME
    AddButton "Proteksi Sheet", "Memproteksi Pivot", 225, "protectDoc"
    AddButton "Refresh Data", "Refresh Pivot", 459, "refreshDoc"

    ' call AddButton more times for more buttons '
    With Application.CommandBars(TOOLBARNAME)
        .Visible = True
        .Position = msoBarTop
    End With
End Sub

Public Sub AddButton(caption As String, tooltip As String, faceId As Long, methodName As String)
Dim Btn As CommandBarButton
Set Btn = Application.CommandBars(TOOLBARNAME).Controls.Add
With Btn
    .Style = msoButtonIcon
    .faceId = faceId
    ' choose from a world of possible images in Excel: see http://www.ozgrid.com/forum/showthread.php?t=39992 '
    .OnAction = methodName
    .TooltipText = tooltip
End With
End Sub

Public Sub DeleteCommandBar()
    Application.CommandBars(TOOLBARNAME).Delete
 End Sub

on ThisWorkbook

'called when add-in installed
Private Sub Workbook_AddinInstall()
    Call Module1.ShowToolbar
End Sub

'called when add-in uninstalled
Private Sub Workbook_AddinUninstall()
    Call Module1.DeleteCommandBar
End Sub

Cannot Run Macro Created with Add-in in Excel

2 Answers