The Chart object model

When you first start exploring the object model for a Chart object, you'll probably be very confused -which is not surprising the object model is very confusing. It's also very deep. For example, assume that you want to change the title displayed in an embedded chart. The top-level object, of course, is the Application object Excel . The Application object contains a Workbook object, and the Workbook object contains a Worksheet object. The Worksheet object contains a ChartObject object, which...

Installing the Enhanced Data Form addin

To try out the Enhanced Data Form, install the add-in 1. Copy the dataform2.xla file from the CD-ROM to a directory on your hard drive. 2. In Excel, choose Office Excel Options. 3. In the Excel Options dialog box, click the Add-Ins tab. 4. Select Excel Add-Ins from the Manage drop-down list and click Go to display the Add-Ins dialog box. 5. In the Add-Ins dialog box, click Browse and locate the dataform2 . xla in the directory from Step 1. After performing these steps, you can access the...

Adding sound to your applications

The example in this section adds some sound capability to Excel. Specifically, it enables your application to play WAV or MIDI files. For example, you might like to play a short sound clip when a dialog box is displayed. Or maybe not. In any case, if you want Excel to play WAV or MIDI files, this section has what you need. CD- The examples in this section are available on the companion CD-ROM in a file named The following example contains the API function declaration plus a simple procedure to...

Creating User Form Templates

You might find that when you design a new UserForm, you tend to add the same controls each time. For example, every UserForm might have two CommandButtons that serve as OK and Cancel buttons. In the previous section, I describe how to create a new control that combines these two customized buttons into a single control. Another option is to create your UserForm template and then export it so it can be imported into other projects. An advantage is that the event handler code for the controls is...

Determining the number of printed pages

If you need to determine the number of printed pages for a worksheet printout, you can use Excel's Print Preview feature and view the page count displayed at the bottom of the screen. The VBA procedure that follows calculates the number of printed pages for the active sheet by counting the number of horizontal and vertical page breaks MsgBox ActiveSheet.HPageBreaks.Count 1 _ ActiveSheet.VPageBreaks.Count 1 amp pages The following VBA procedure loops through all worksheets in the active workbook...

Executing Ribbon commands

In previous versions of Excel, programmers created custom menus and toolbars by using the CommandBar object. In Excel 2007, the CommandBar object is still available, but it doesn't work like it has in the past. CROSS- Refer to Chapter 22 for more information about the CommandBar object. The CommandBar object has also been enhanced in Excel 2007. You can use the CommandBar object to execute Ribbon commands using VBA. Many of the Ribbon commands display a dialog box. For example, the statement...

Functions with no argument

Like Sub procedures, Function procedures need not have arguments. Excel, for example, has a few built-in functions that don't use arguments, including RAND, TODAY, and NOW. You can create similar functions. This section contains examples of functions that don't use an argument. CD- A workbook that contains these functions is available on the companion CD-ROM. The file ROM s named ' no argument, xlsm. Here's a simple example of a function that doesn't use an argument. The following function...

Moving items in a List Box

Often, the order of items in a list is important. The example in this section demonstrates how to allow the user to move items up or down in a ListBox. The VBE uses this type of technique to let you control the tab order of the items in a UserForm right-click a UserForm and choose Tab Order from the shortcut menu . Figure 14-13 shows a dialog box that contains a ListBox and two CommandButtons. Clicking the Move Up butto moves the selected item up in the ListBox clicking the Move Down button...

Working with multicolumn List Box controls

Excel Vba Listbox Columns

A normal ListBox has a single column for its contained items. You can, however, create a ListBox that displays multiple columns and optionally column headers. Figure 14-14 shows an example of a multicolumn ListBox th gets its data from a worksheet range. Figure 14-14 This ListBox displays a three-column list with column headers. CD-ROM Figure 14-14 This ListBox displays a three-column list with column headers. CD-ROM This example, named listbox multicolumnl .xlsm , is available on the companion...

Adding items to a List Box control

Before displaying a UserForm that uses a ListBox control, you'll probably need to fill the ListBox with items. Yo can fill a ListBox at design time using items stored in a worksheet range or at runtime using VBA to add the items to the ListBox. The two examples in this section presume that You have a UserForm named userForm1 . This UserForm contains a ListBox control named ListBox1 . The workbook contains a sheet named Sheet1 , and range A1 A12 contains the items to be displayed in th ListBox....