This article describes how to display a UserForm that
welcomes you when you start Microsoft Excel or Microsoft Word. Component Object
Model (COM) add-ins provide a means to centralize common code in a compiled
dynamic-link library (DLL) that can be easily implemented in any Office
application (for example, you can access Microsoft Outlook contacts from Excel,
Word, and Microsoft Access). The easiest way to develop a COM DLL in Microsoft
Office is to work with an add-in project, which provides a designer and a
reference to the IDTExtensibility2
This article assumes that you are familiar with the following
- Creating Visual Basic for Applications (VBA) procedures in
an Office XP application.
- Working with UserForms in VBA.
- Customizing Office XP toolbars.
Create an Add-In Project
You can create an add-in project in any Microsoft Office 2000 or
Microsoft Office XP application when you have the Microsoft Office Developer
2000 or Microsoft Office XP Developer tools installed. This example uses
Microsoft Word 2002.
- Start Microsoft Word 2002.
- From the Tools menu, point to Macro, and then click Visual Basic Editor.
- In the Visual Basic Editor (VBE), click New Project on the File menu.
- In the New Project dialog box, click Add-In Project. This automatically provides an Add-In Designer.
- If the Properties window is not visible, click Properties Window on the View menu.
- In the Properties window, rename the project from
AddInDesigner1 to WelcomeAddIn.
- From the Insert menu, click UserForm to insert a UserForm.
- From the toolbox, add a label and a command button to the
- In the Properties window, change the attributes of the
controls to match the values in the following table:
Collapse this tableExpand this table
- From the Insert menu, click Module to insert a standard module.
- Add the following global string variable declaration to the
Public gstrUserName As String
'This variable will be used to store the name of the user as defined
'in the Options dialog boxes (from the Tools menu) in Word and Excel.
- In the module, add the following code to create a public
Sub procedure named DisplayForm to display the form on the screen:
Public Sub DisplayForm()
- View the code window for the frmWelcome form, and add the
following code to the UserForm_Initialize event to concatenate the label caption with the string variable:
lblMessage.Caption = lblMessage.Caption & " " & gstrUserName
- Add the following code to the Click event for the CommandButton cmdOK to unload the form:
Change the Properties of the Add-In Designer
Follow these steps to change the properties of the designer so
that the add-in will start as soon as Microsoft Excel is started:
- In Project Explorer, double-click on the component to
return to the Add-In Designer.
- On the General tab of the designer, change the properties of the designer to
match the values in the following table:
Collapse this tableExpand this table
|AddIn Display Name||FunFormMessage|
|AddIn Description||This contains code that is
specific to Excel|
|Application Version||Microsoft Excel 10.0|
|Initial Load Behavior||Startup|
Implement the IDTExtensibility2 Events
- From the View menu, click Code to view the code module behind the designer.
- From the Tools menu, click References, and set a reference to the Microsoft Excel Object
- In the Object drop-down list, click AddinInstance. In the Procedure drop-down list, click OnConnection.
This event is useful for creating a CommandBar button
or to associate code with an event such as an application event when the add-in
is connected to the host application.
- Add the following code to the AddinInstance_OnConnection event procedure to store the user name in the global variable strUserName and to call the DisplayForm procedure:
gstrUserName = Application.UserName
- In the VBE, click Save, and name the project Welcome.vba.
Add a Second Designer
In this procedure, you add a second designer to implement the
same functionality in Microsoft Word.
- Create another Add-In Project in the VBE. AddInProject2 is
created by default.
- In the Project Explorer, drag the new Add-In Designer
interface (AddInDesigner1) into your existing add-in project. There will be two designers
in the project.
- In Project Explorer, right-click AddInProject2, and then click Close Project to remove the project that you just created.
- When you receive the following message or similar
Project AddInProject2 has been modified. Do you wish to
- Change the properties for the designer to specify Word 10 as the target application and to load at Startup.
- Add the following code to the new Word designer's AddinInstance_OnConnection event procedure, which is the same as the previously written code
for the Excel designer's OnConnection event:
gstrUserName = Application.UserName
Debug the Add-In
- If you prefer, add appropriate break points to the
- In the VBE, click Run Project from the Run menu to run the add-in.
- Start Microsoft Excel. When the project starts, you may see
a dialog box for the AddIn Designer. Click the option to start an application,
and browse to Excel.exe. If this dialog box does not appear, click Start, point to Programs, and then click Microsoft Excel to start Excel. Your UserForm should appear.
- If the COM Add-In Manager is not already available, add the
COM Add-Ins command to a toolbar as follows:
- On the Tools menu, click Customize.
- In the Customize dialog box, on the Commands tab, drag the COM Add-Ins command from the Tools category onto a toolbar or onto the Tools menu CommandBarPopup object.
- Click the COM Add-Ins command bar to display the COM Add-In Manager (CAM). The list in
the CAM displays all of the add-ins that are loaded.
- To test the OnConnection event, clear the Welcome Add-In check box in the CAM, and then click OK. Redisplay the CAM, select the Welcome check box, and then click OK. The UserForm should appear again.
- Close the host application (either Word or Excel), and
click Stop Project from the Run menu in the VBE to stop running the project.
Verify That the Add-In Works
Now you can compile the add-in and verify that it works on
- Save the project in the VBE. This is your editable version.
The compiled state does not allow you to make modifications.
- From the File menu, click Make Welcome.DLL.
- Use the regsvr32 command to register the DLL as follows:
- From the Windows Start menu, click Run.
- In the Open text box, type the following text:
regsvr32 <path>\Welcome.DLL where <path> is the file system path to the DLL.
- Click OK to confirm the registration of the DLL.
- Run Excel and/or Word. Your UserForm should appear. If you
do not see your UserForm, click COM AddIns from the Tools menu, and then select the Welcome check box.
: The easiest way to distribute the DLL is to use the Package and
- Make sure that you write short, efficient code for when the
add-in is loaded into memory. For example, if your OnConnection event manipulates database data at startup, your users will think
that the computer has stopped responding (hung).
- Be careful when you use UserForms. If the user clicks on
the host application, the user may think that he or she has lost the form
because it is hidden behind the host application. Instead, make your forms
modal to overcome this problem. To make a form modal, use the vbModal constant as follows:
To see where this code fits in reference to this example, see the code
in Step 12 of the Create an Add-In
Refer to the \Samples\Working with AddIns\VBA_COM_AddIn
folder on the Office XP Developer CD for an AddIn template.
For additional information about creating CommandBar buttons, visit the following Microsoft Developer Network (MSDN) Web site: