无法在Excel中运行使用加载项创建的宏

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.

我创建了一个xla(excel加载项),它具有保护文档的功能(这样用户可以在不知道密码的情况下保护文档)。此xla将添加到需要此功能的每个excel文件中。

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

当xla安装或添加到excel时,保护按钮将添加到最后一个菜单中。但是当我点击按钮时,会出现错误

"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."

“无法运行宏Pivot Add-In 0.2'!protectSheet'”。宏可能在此工作簿中不可用,或者可能禁用所有宏。“

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

onclicked事件处理程序的代码是protectSheet,请参阅下面的源代码:

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"

2 个解决方案

#1

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)

将所有函数移动到module1后,在ThisWorkbook上保留调用函数,错误现在消失了。似乎我必须在同一个文件中定义调用/使用常量(Public Const TOOLBARNAME =“PivotTools”)的所有功能(在我的情况下在module1中)

on module1 file

在module1文件上

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

#2

I had this issue, but found that I had my module named the same as my sub (e.g. module named as "InsertLineID" and the sub was "InsertLineID").

我有这个问题,但发现我的模块名称与我的sub相同(例如模块名为“InsertLineID”,sub是“InsertLineID”)。

Changing the module name to "LineID" and leaving the sub as "InsertLineID" worked a treat for me!

将模块名称更改为“LineID”并将子项保留为“InsertLineID”对我来说是一种享受!

#1