Displaying a Spreadsheet in a User Form

Userform Chart

Not content to simply display a chart in a UserForm How about an entire spreadsheet Figure 15-15 shows an example of a UserForm that contains a Microsoft Office Spreadsheet 11.0 control. This control can contain an entire interactive spreadsheet, complete with formulas and formatting. In fact, this Spreadsheet control has a significant advantage over a standard Excel sheet Its dimensions are 18,278 columns x 262,144 rows. This is nearly 300 times as many cells as a standard Excel worksheet...

Creating a Splash Screen

Excel Splash Screen Example

Some developers like to display some introductory information when the application is opened. This is commonly known as a splash screen. You are undoubtedly familiar with Excel's splash screen, which appears for a few seconds when Excel is loading. You can create a splash screen for your Excel application with a UserForm. This example is essentially a UserForm that displays automatically and then dismisses itself after five seconds. Follow these instructions to create a splash screen for your...

Creating The Chart For An Owc Chartspace Control

Vba Userform Chartspace Type

The following code, which is located in the UserForm code module, creates a chart using data stored on a worksheet. The category labels are in A2 A13, and the chart data is in B2 B13. It assumes that the ChartSpace object is named ChartSpacel. Dim Chartl As ChChart 'WCChart Dim Seriesl As ChSeries 'WCSeries Dim r As Integer Dim XValues 1 To 12 Dim DataValues 1 To 12 .HasTitle True .Title.Caption Range B1 End With XValues r - 1 Cells r, 1 DataValues r - 1 Cells r, 2 Next r Set Series1...

Handling Multiple User Form Buttons With One Event Handler

Every CommandButton on a UserForm must have its own procedure to handle its Click event. For example, if you have two CommandButtons, you'll need at least two event handler procedures Private Sub CommandButtonl_Click ' Code goes here End Sub Private Sub CommandButton2_Click ' Code goes here End Sub In other words, you cannot assign a macro to execute when any CommandButton is clicked. Each Click event handler is hard-wired to its CommandButton. You can, however, have each event handler call...

The Before RightClick event

When the user right-clicks in a worksheet, Excel displays a shortcut menu. If, for some reason, you'd like to prevent the shortcut menu from appearing in a particular sheet, you can trap the RightClick event. The following procedure sets the Cancel argument to True, which cancels the RightClick event and thereby cancels the shortcut menu. Instead, a message box is displayed. Private Sub Worksheet_BeforeRightClick _ ByVal Target As Excel.Range, Cancel As Boolean Cancel True MsgBox The shortcut...

Using VBA to activate a chart

When a user clicks on an embedded chart, the chart is activated. Your VBA code can activate an embedded chart with the Activate method. Here's an example ActiveSheet.ChartObjects Chart 1 .Activate If the chart is on a chart sheet, use a statement like this After a chart is activated, you can refer to it in your code with ActiveChart. For example, the following instruction displays the name of the active chart. If there is no active chart, the statement generates an error To modify a chart with...

Displaying a chart in a User Form

Showing Graph Userform

In Chapter 15, I describe a way to display a chart in a UserForm. The technique saves the chart as a GIF file and then loads the GIF file into an Image control on the UserForm. The example in this section uses that same technique but adds a new twist The chart is created on the fly and uses the data in the row of the active cell. Figure 18-9 shows an example. Figure 18-9 The chart in this UserForm is created on-the-fly from the data in the active row. Figure 18-9 The chart in this UserForm is...

Creating a Pivot Table from an External Database

In the preceding example, the source data was in a worksheet. As you probably know, Excel also enables you to use an external data source to create a pivot table. The example in this section demonstrates how to write VBA code to create a pivot table based on data stored in an Access database file. The Access database consists of a single table that is identical to the data used in the previous example. The code that creates the pivot table is shown in Listing 17-3. It assumes that the...

Working with multicolumn List Box controls

Vba Column Headers Listbox

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 that gets its data from a worksheet range. Figure 14-14 This ListBox displays a three-column list with column headers. Figure 14-14 This ListBox displays a three-column list with column headers. To set up a multicolumn ListBox that uses data stored in a worksheet range, follow these...

Creating a clock chart

Set Text Box Digital Clock Vba

Figure 18-19 shows an XY chart formatted to look like a clock. It not only looks like a clock, but it also functions as a clock. I can't think of a single reason why anyone would need to display a clock like this on a worksheet, but creating the workbook was challenging, and you might find it instructive. start Clock stop Clcck 0 Analog Clock start Clock stop Clcck 0 Analog Clock Figure 18-19 This clock is fully functional and is actually an XY chart in disguise. Figure 18-19 This clock is...

About the Enhanced Data Form

The Enhanced Data Form features the following enhancements, as listed in Table 15-1. Table 15-1 COMPARING THE ENHANCED DATA FORM WITH THE EXCEL DATA FORM Handles any number of records and fields. Dialog box can be displayed in any size that you like. Fields can consist of either Text Box or ComboBox controls. Record displayed in the dialog box is always visible onscreen and is highlighted so you know exactly where you are. At startup, the dialog box always displays the record at the active...

Storing multiple charts on a chart sheet

Word Graph Example

Most Excel users who take the time to think about it would agree that a chart sheet holds a single chart. Most of the time, that's a true statement. However, it's certainly possible to store multiple charts on a single chart sheet. In fact, Excel lets you do this directly. If you activate an embedded chart and then choose Chart Location, Excel displays its Chart Location dialog box. If you select the As New Sheet option and specify an existing chart sheet as the location, the chart will appear...

How can I print the workbooks full path and filename in a page header

If you use Excel 2002 or later, you can take advantage of a new feature in the Page Setup dialog box. When this dialog is displayed, click the Header Footer tab and click Custom Header. You'll find a new icon that inserts the code to print the full path and filename of the workbook. Note, however, that if the workbook has not been saved, the path name might be incorrect. It uses the default workbook path. For older versions of Excel, you need to use a VBA macro and take advantage of the...

Method Use the OWC Chart Space control

Owc11 Chartspace

As I mention in Chapter 13, a UserForm may contain other controls that aren't normally included in the Toolbox. Microsoft includes the Office Web Components OWC with Office 2003, and you can use the Web Component controls in your UserForms. Figure 15-13 shows an example of a UserForm that contains a This technique does not allow you to display an existing Excel chart on a UserForm. Rather, you must write code that creates the chart in the Figure 15-13 This UserForm contains a ChartSpace...

Selecting Ranges from a User Form

Several of Excel's built-in dialog boxes allow the user to specify a range. For example, the Goal Seek dialog box asks the user to select two ranges. The user can either type the range name directly or use the mouse to point and click in a sheet to make a range selection. Your UserForms can also provide this type of functionality, thanks to the RefEdit control. The RefEdit control doesn't look exactly like the range selection control used in Excel's built-in dialog boxes, but it works in a...

Enabling events for an embedded chart

As I note in the preceding section, Chart events are automatically enabled for chart sheets but not for charts embedded in a worksheet. To use events with an embedded chart, you need to perform the following steps. In the Visual Basic Editor VBE window, select your project in the Project window and choose Insert Class Module. This will add a new empty class module to your project. Then use the Properties window to give the class module a more descriptive name such as clsChart . Renaming the...

Creating interactive HTML files

If you're still with me at this point, it's time to introduce yet another level of complexity. Excel can save HTML files that include spreadsheet interactivity. In other words, when the HTML file is displayed in a browser, the user can actually interact with the document as a spreadsheet enter data, change formulas, adjust cell formatting, see live charts, and even drag data around in pivot tables. In Excel 2003, you can create an HTML file with interactivity from a multisheet workbook. In...

Listing all Command Bar objects

If you're curious about the objects in the CommandBars collection, the following procedure should be enlightening. Executing this procedure generates a list (as shown in Figure 22-6) of all CommandBar objects in the CommandBars collection. For Excel 2003, it lists a total of 126 built-in command bars, plus any custom menu bars or toolbars. For each command bar, the procedure lists its Index, Name, and Type property settings (displayed as Toolbar, Menu Bar, or Shortcut) as well as whether it's a...

Changing Excel menu elements

If you get bored with Excel's standard menu text, you can change it to something else for instance, you can change the Tools menu to the Miscellaneous menu. You can also assign your own macros to built-in menu items. You have many other options for changing menu elements, including rearranging the order of the menus on a menu bar for example, to make the Help menu appear first instead of last . If you've customized menus in Excel 5 or Excel 95, you can pretty much forget everything that you...

Using the Multi Page Control in a User Form

Page Tab Userform Excel

The MultiPage control is very useful for UserForms that must display many controls. The MultiPage control lets you group the choices and place each group on a separate tab. Figure 14-18 shows an example of a UserForm that contains a MultiPage control. In this case, the control has three pages, each with its own tab. Figure 14-18 MultiPage groups your controls on pages, making them accessible from a tab. Figure 14-18 MultiPage groups your controls on pages, making them accessible from a tab. The...

Reading from and writing to the Registry

Most Windows applications use the Windows Registry database to store settings. See Chapter 4 for some additional information about the Registry. Your VBA procedures can read values from the Registry and write new values to the Registry. Doing so requires the following Windows API declarations Private Declare Function RegOpenKeyA Lib ADVAPI32.DLL _ ByVal hKey As Long, ByVal sSubKey As String, _ ByRef hkeyResult As Long As Long Private Declare Function RegCloseKey Lib ADVAPI32.DLL _ ByVal hKey As...

Adding new ActiveX controls

UserForms can contain other ActiveX controls developed by Microsoft or other vendors. To add an additional ActiveX control to the toolbox, right-click the Toolbox and select Additional Controls. This will display the dialog box shown in Figure 13-14. The Additional Controls dialog box lists all ActiveX controls that are installed on your system. Select the control s that you want to add then click OK to add an icon for each selected control. Microsoft Office PivotTable 9.0 Tj Microsoft Office...

Advanced User Form Techniques

This chapter picks up where Chapter 13 left off. Here you'll find additional examples of UserForms. Displaying a progress indicator three techniques Creating a wizard an interactive series of dialog boxes Creating a function that emulates VBA's MsgBox function Handling multiple objects with a single event handler Using a dialog box to select a color Displaying cell information in a UserForm Displaying a chart in a UserForm two techniques Displaying a complete spreadsheet in a UserForm Using an...

Whats the difference between using Select and Application Goto

The Select method of the Range object selects a range on the active worksheet only. Use Application.Goto to select a range on any worksheet in a workbook. Application.Goto might or might not make another sheet the active sheet. The Goto method also lets you scroll the sheet so that the range is in the upper-left corner. What's the difference between activating a range and selecting a range In some cases, the Activate method and the Select method have exactly the same effect. But in other cases,...

Identifying system settings

Generally, you cannot assume that the end user's system is set up like the system on which you develop your application. For international applications, you need to be aware of the following settings Decimal separator The character used to separate the decimal portion of a value Thousands separator The character used to delineate every three digits in a value List separator The character used to separate items in a list You can determine the current separator settings by accessing the...