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 library.
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 UserForm.
In the Properties window, change the attributes of the controls to match the values in the following table:
From the Insert menu, click Module to insert a standard module.
Add the following global string variable declaration to the standard module:
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() frmWelcome.Show vbModalEnd Sub
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:
If you prefer, add appropriate break points to the code.
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.
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 Project section.