How do I make an add-in from my project in Excel 97?
	How do I create custom menus and toolbars in Excel 97?
	How do I get a separator bar between my CommandBarControls?
	In Excel 97, how can I use a text file containing VBA code to create a code module in a workbook at run-time?
	Where do I write VBA programs in Excel 97?
	How do I add modules and dialogs to my Excel 97 VBA Project?
	What is the difference between Excel 97 UserForms and the old DialogSheets?
	What are events and how do I use them?

How do I make an add-in from my project in Excel 97?
Keywords: Excel 97, Add-ins
Posted July 18, 1997

There are two ways to create an add-in from your project in Excel 97, both of them different from previous versions of Excel. The first is to choose File/Save As from the Excel menu, then change the Save As Type to Microsoft Excel Add-In (*.xla), which is the last item in the file types list. The second method is to set the IsAddin property of the Workbook object to True. This must be done from within the Visual Basic Editor (VBE). Open the VBE (ALT-F11) and select the workbook object, usually named ThisWorkbook, for your project from the Project Explorer window (CTRL-R). The Properties window (F4) will now show a list of properties for the workbook object. Locate the IsAddin property and change its value to True, then save your project (CTRL-S).

Note, there are two significant differences between add-ins created in previous version of Excel and those created in Excel 97. In previous versions of Excel, creating an add-in also compiled your VBA code. This is no longer the case in Excel 97. To compile your code, choose Debug/Compile VBAProject from the VBE menu, then save your project to store it in compiled form. Also, the code for add-ins created in previous versions of Excel was protected from viewing. This does not happen automatically in Excel 97. To lock your code from viewing, choose Tools/VBAProject Properties/Protection from the VBE menu. Check the Lock Project for Viewing option and then enter a password. Save your project, then close it and reopen it for the locking to take effect.

How do I create custom menus and toolbars in Excel 97?
Keywords: Excel 97, Add-ins
Posted July 18, 1997

In Excel 97, MenuBars and Toolbars are both part of the new CommandBars object model. The VBA code required to create custom menus and toolbars is very similar, enough so that it is possible to create one table-driven solution that will build both toolbars and menu bars at the same time (see the Baarns Developer JumpStart for more details). The following code sample shows how to create a simple custom menu bar using the CommandBars object model. In order to run this code, you must set a reference in the Visual Basic Editor (VBE) to the Microsoft Office 8.0 object library (Choose Tools/References from the VBE menu and put a check mark beside this name).

''' This routine adds a custom MenuBar.
Sub BuildMenuDemo()
    Dim cbrMenuBar As CommandBar
    Dim ctlMenu As CommandBarPopup
    Dim ctlMenuItem1 As CommandBarButton
    Dim ctlMenuItem2 As CommandBarPopup
    Dim ctlSubMenu As CommandBarButton
    Dim lCount As Long
    ''' Create MenuBar
    Set cbrMenuBar = CommandBars.Add("MyBar", msoBarTop, True, True)
    cbrMenuBar.Visible = True
    ''' Add menu
    Set ctlMenu = cbrMenuBar.Controls.Add(msoControlPopup)
    ctlMenu.Caption = "&My Menu"
    ''' Add MenuItems
    Set ctlMenuItem1 = ctlMenu.Controls.Add(msoControlButton)
    ctlMenuItem1.Caption = "Menu Item &1"
    Set ctlMenuItem2 = ctlMenu.Controls.Add(msoControlPopup)
    ctlMenuItem2.Caption = "Menu Item &2"
    ''' Add SubMenuItems.
    For lCount = 1 To 3
        Set ctlSubMenu = ctlMenuItem2.Controls.Add(msoControlButton)
        ctlSubMenu.Caption = "Submenu &" & lCount
    Next lCount
End Sub

''' This routine deletes the custom MenuBar added above.
Sub DeleteMenu()
    CommandBars("MyBar").Delete
End Sub

How do I get a separator bar between my CommandBarControls ?
Keywords: Excel 97, Add-ins
Posted July 18, 1997

In the Excel 97 CommandBarControls object model, separator bars are added by setting the BeginGroup property of the control that is below (in the case of menus) or to the right (in the case of toolbar buttons) of the position where you want the separator bar to go. For instance,

		MyControl.BeginGroup = True

adds a separator above or to the left of MyControl depending on the context.

In Excel 97, how can I use a text file containing VBA code to create a code module in a workbook at run-time?
Keywords: Excel 97, VBA
Posted July 18, 1997

In Excel 97 VBA, code modules and other parts of a VBA project belong to the VBComponents collection of the VBProject object for a given workbook. Creating a new code module at run-time requires you to add a new item to the VBComponents collection. To access these objects, you first need to set a reference to the Visual Basic for Applications Extensions library in the project where the module creating code will go. To do this, activate that project in the VBE, choose Tools/References from the VBE menu and place a check mark beside the VBA Extensions object library. Then, to add a new module to an existing workbook use the following procedure:

Sub AddNewModule()
    Dim szBookName As String
    Dim szTextFile As String
    Dim szPath As String
    ''' Your path, workbook name and text file name goes here.
    szPath = "d:\"
    szBookName = "Book1.xls"
    szTextFile = "Template.txt"
    With Workbooks(szBookName).VBProject.VBComponents
        .Import szPath & szTextFile
    End With
End Sub

Where do I write VBA programs in Excel 97?
Keywords: Excel 97, VBA, VBE
Posted March 30, 1997

In Excel 97, all VBA programming is done using the new Visual Basic Environment (VBE). This programming environment is completely separate from Excel and is common across all Office 97 applications. You can access the VBE from Excel 97 either by choosing Tools/Macro/Visual Basic Editor from the Excel menu or by pressing ALT-F11 on the keyboard.

In Excel 97, all of the VBA code modules, forms and other objects associated with your workbook are now referred to as that workbook's VB Project. Upon opening the VBE for the first time, you will see two windows: the Project Explorer and the Properties Window.

The Project Explorer displays a tree view style list of all the VBA components in your project. At the top of the Project Explorer you will see three toolbar buttons. The left-most button is called Toggle Folders. This button toggles the Project Explorer view between showing each type of VB Project component in a separate folder (i.e. modules in one folder, Excel objects in another) and showing all VB components in one large tree.

The Project Properties window shows a list of properties that apply to the currently selected object. You can modify the object's properties directly by making new entries in this window. For example, if you select a worksheet, the Properties Window will show a list of eleven properties that can be modified directly from the VBE.

How do I add modules and dialogs to my Excel 97 VBA Project?
Keywords: Excel 97, Modules, UserForms, VBA, VBE
Posted March 30, 1997

To add a code module to your project, open the Visual Basic Editor, as described above, and choose Insert/Module from the VBE menu. A new module will appear in it's own window within the VBE. As in earlier versions of Excel, the module will appear with a default name of Module1. You can rename the module using the (Name) property shown in the Properties Window. You enter and run procedures in Excel 97 modules just like you did in previous versions of Excel.

Dialog sheets, found in previous versions of Excel, have been replaced by UserForms in Excel 97. UserForms are added to your project in much the same way that modules are; by choosing Insert/UserForm from the VBE menu. After choosing this menu item, a new UserForm will appear, along with the Toolbox used for adding controls.

What is the difference between Excel 97 UserForms and the old DialogSheets?
Keywords: Excel 97, User Forms
Posted March 30, 1997

UserForms in Excel 97 are very different from the old DialogSheets. The first thing you will notice is that there are no buttons on a new UserForm. You must manually add and code for every control that you want to appear on your UserForm. Controls are added to a UserForm from the Toolbox. Each button displayed in the Toolbox is a control available to add to your UserForm. To add a CommandButton to your UserForm, click the CommandButton control in the Toolbox and then drag out a CommandButton on the UserForm.

Displaying UserForms is also somewhat different in Excel 97. In previous versions of Excel, DialogSheets were simply special sheets in your workbook. UserForms are not actually part of your workbook anymore. When referencing a UserForm in your code you refer to it by name rather than as a sheet. For instance, in Excel 5/95 you would show a dialog in the following manner:

   ThisWorkbook.DialogSheets("MyDialog").Show

In Excel 97, a UserForm is displayed like this:

   UserForm1.Show

In Excel 5/95, any CommandButton formatted to dismiss the dialog would remove the dialog from the screen when it was clicked. In Excel 97 you must explicitly unload your UserForms with:

   UnLoad UserForm1

Also, in previous versions of Excel, the Show method returned True or False depending on whether the user clicked the OK or Cancel button. This is no longer the case in Excel 97. You must add code to your OK and Cancel buttons to help you determine which one was selected (see the Event FAQ below for instructions on how to add code to a CommandButton).

What are events and how do I use them?
Keywords: Excel 97, Events
Posted March 30, 1997

Events are triggered by certain specific actions, taken either by your code or by the user. For instance, when a workbook is saved, a Workbook_BeforeSave event is triggered. Likewise, when a CommandButton is clicked, a CommandButton_Click event is triggered. Each event runs a specific procedure within the code module of the object to which the event applies. If CommandButton1 is located on UserForm1, then clicking CommandButton1 will fire the following event procedure in the code module behind UserForm1:

   Private Sub CommandButton1_Click()

   End Sub

If CommandButton1 happens to be your Cancel button, you can respond to this click event by unloading UserForm1:

   Private Sub CommandButton1_Click()
   ''' UnLoad UserForm1
   End Sub

Workbooks, Worksheets, and UserForms all respond to events and all have code modules where the event procedures can be located. To locate the Click event for a CommandButton on a UserForm and use that event to close the form, follow these steps:

• Add a UserForm to your project and draw one CommandButton on the UserForm. Then select the new UserForm in the Project Explorer.

• The upper left toolbar button in the Project Explorer window is called View Code. After you have selected your UserForm, click this button. The code module associated with that UserForm will now appear on your screen.

• At the top of the UserForm code module you will see two dropdowns. The left dropdown contains a list of objects associated with the UserForm while the right dropdown contains a list of events available for the object currently selected in the left dropdown. Open the left dropdown and select CommandButton1.

• By default, the CommandButton1_Click procedure will appear in the UserForm code module. This is the event procedure that you will be using. You can open the right dropdown and see what other events are available for the CommandButton object.

• Inside the CommandButton1_Click procedure add the following line of code:

   UnLoad UserForm1

• Now, from the VBE menu choose View/Immediate Window. The Immediate Window appears on your screen. Inside the Immediate Window type UserForm1.Show followed by ENTER. This will display UserForm1. Click the CommandButton on UserForm1. The UserForm disappears. This occurred because you placed the UnLoad statement in the CommandButton click event.

Back to Top

Back to the FAQ Table of Contents

I just got lost in thought. It was unfamiliar territory.