' ------------------------------------------
' This is the shared code module --- Randy
' ------------------------------------------
Option Explicit
' Global variable to store reference to host application.
Public gobjAppInstance As Object
' Constants for characters surrounding ProgID.
Public Const PROG_ID_START As String = "!<"
Public Const PROG_ID_END As String = ">"
' Constants for menu item in Office application.
Public Const CBR_NAME As String = "Tools"
Public Const CTL_CAPTION As String = "My &COM Add-in"
Public Const CTL_KEY As String = "MyCOMAddIn"
Public Const CTL_NAME As String = "My COM Add-in"
Sub AddInErr(errX As ErrObject)
' Displays message box with error information.
Dim strMsg As String
strMsg = "An error occurred in the COM add-in named '" _
& App.Title & "'." & vbCrLf & "Error #:" & errX.Number _
& vbCrLf & errX.Description
MsgBox strMsg, , "Error!"
End Sub
Function CreateAddInCommandBarButton(ByVal Application As Object, _
ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object) As Office.CommandBarButton
' This procedure assigns a reference to the Application
' object passed to the OnConnection event to a global
' object variable. It then creates a new command bar
' button and returns a reference to the button to the
' OnConnection event procedure. The advantage to
' putting this code in a public module is that if you
' have more than one add-in designer in the project, you can
' call this procedure from each of them rather than
' duplicating the code.
Dim cbrMenu As Office.CommandBar
Dim ctlBtnAddIn As Office.CommandBarButton
On Error GoTo CreateAddInCommandBarButton_Err
' Return reference to Application object and store it in public variable
' so that other procedures in add-in can use it.
Set gobjAppInstance = Application
' Return reference to command bar.
' This is the line that errors --- Randy
Set cbrMenu = gobjAppInstance.CommandBars(CBR_NAME)
' Add button to call add-in from command bar, if it doesn't
' already exist.
' Constants are declared at module level.
' Look for button on command bar.
Set ctlBtnAddIn = cbrMenu.FindControl(Tag:=CTL_KEY)
If ctlBtnAddIn Is Nothing Then
' Add new button.
Set ctlBtnAddIn = cbrMenu.Controls.Add(Type:=msoControlButton, _
Parameter:=CTL_KEY)
' Set button's Caption, Tag, Style, and OnAction properties.
With ctlBtnAddIn
.Caption = CTL_CAPTION
.Tag = CTL_KEY
.Style = msoButtonCaption
' Use AddInInst argument to return reference
' to this add-in.
.OnAction = PROG_ID_START & AddInInst.ProgId _
& PROG_ID_END
End With
End If
' Return reference to new commandbar button.
Set CreateAddInCommandBarButton = ctlBtnAddIn
CreateAddInCommandBarButton_End:
Exit Function
CreateAddInCommandBarButton_Err:
' Call generic error handler for add-in.
AddInErr Err
Resume CreateAddInCommandBarButton_End
End Function
Function RemoveAddInCommandBarButton(ByVal _
RemoveMode As AddInDesignerObjects.ext_DisconnectMode)
' This procedure removes the command bar button for
' the add-in if the user disconnected it.
On Error GoTo RemoveAddInCommandBarButton_Err
' If user unloaded add-in, remove button. Otherwise, add-in is
' being unloaded because application is closing; in that case,
' leave button as is.
If RemoveMode = ext_dm_UserClosed Then
On Error Resume Next
' Delete custom command bar button.
gobjAppInstance.CommandBars(CBR_NAME).Controls(CTL_NAME).Delete
On Error GoTo RemoveAddInCommandBarButton_Err
End If
RemoveAddInCommandBarButton_End:
Exit Function
RemoveAddInCommandBarButton_Err:
AddInErr Err
Resume RemoveAddInCommandBarButton_End
End Function
' ------------------------------------------
' This is the code from the Designer Module --- Randy
' ------------------------------------------
Option Explicit
' Implement extensibility library.
Implements IDTExtensibility2
' Private module-level variables.
Private WithEvents p_mctlBtnEvents As Office.CommandBarButton
Private p_frmCOMAddIn As frmCOMAddIn
Private Sub AddinInstance_Initialize()
' Initialize event procedure for add-in designer class.
' Create private instance of form, but don't show it.
' Creating a private reference to the form and destroying
' it when the add-in is disconnected ensures that no
' instances of the form will be left in memory.
Set p_frmCOMAddIn = New frmCOMAddIn
End Sub
Private Sub AddinInstance_Terminate()
' Terminate event procedure for add-in designer class.
' Destroy private instance of form.
Unload p_frmCOMAddIn
Set p_frmCOMAddIn = Nothing
End Sub
'------------------------------------------------------
' This event occurs when the user clicks the menu
' command for the add-in in the Office application.
'------------------------------------------------------
Private Sub p_mctlBtnEvents_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
On Error GoTo Event_Err
' Show form.
p_frmCOMAddIn.Show
Event_End:
Exit Sub
Event_Err:
AddInErr Err
Resume Event_End
End Sub
Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)
' Debug.Print OnBeginShutdown
End Sub
'------------------------------------------------------
' Runs when add-in loads in Office application
' Can run when user loads add-in from Office COM Add-Ins dialog box,
' on startup, or when another application loads add-in.
'------------------------------------------------------
Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _
ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, custom() As Variant)
' Call shared code to create new command bar button
' and return reference to it. Assign reference to
' event-ready CommandBarButton object declared with
' WithEvents within this module.
Set p_mctlBtnEvents = CreateAddInCommandBarButton(Application, ConnectMode, _
AddInInst)
End Sub
'-----------------------------------------------------------
' Runs when add-in is unloaded from Office application.
' Can run when user manually unloads add-in from Office
' COM Add-Ins dialog box, when application shuts down, or
' when another application unloads the add-in.
'-----------------------------------------------------------
Private Sub IDTExtensibility2_OnDisconnection(ByVal _
RemoveMode As AddInDesignerObjects.ext_DisconnectMode, _
custom() As Variant)
' Call common procedure to disconnect add-in.
RemoveAddInCommandBarButton RemoveMode
End Sub
Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)
'Debug.Print "OnStartupComplete"
End Sub
Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)
'Debug.Print "OnAddInsUpdate"
End Sub
