Este código entrará com o procedimento referente ao item de menu que foi clicado, baseado na referencia .Parameter.
Daí ele vai abrir outra paste de trabalho e executar o respecitivo procedimento. quando vou fazer o teste, aparece a mensagem de que não consegue achar o procedimento naquela pasta de trabalho.
Sub RunUtility()
Dim WkbName As String
Dim ProcName As String
WkbName = Application.CommandBars.ActionControl.Parameter
If WkbName = "" Or WkbName = "ThisWorkbook" Then WkbName = ThisWorkbook.Name
ProcName = Application.CommandBars.ActionControl.Tag
On Error GoTo WkbNotFound
If Not IsBookOpen(WkbName) Then
Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & WkbName
End If
On Error GoTo ProcNotFound
Application.Run WkbName & "!" & ProcName
Exit Sub
WkbNotFound:
MsgBox "Cannot Find workbook" & WkbName & "in" & ThisWorkbook.Path, vbCritical, "Test Add-In"
Exit Sub
ProcNotFound:
MsgBox "Cannot find procedure" & ProcName & "in" & WkbName, vbCritical, "Test Add-In"
Exit Sub
End Sub
Private Function IsBookOpen(sWkbName) As Boolean
Dim sName As String
On Error GoTo WkbNotOpen
IsBookOpen = True
sName = Workbooks(sWkbName).Name
Exit Function
WkbNotOpen:
IsBookOpen = False
End Function