|Microsoft Office Access 2003
Microsoft Access 2002
Why use script?
Even though it's easy to create Web pages that contain interactive charts, spreadsheets, and PivotTable® lists, using script with the Microsoft Office Web Components gives you added power, flexibility, and control when it comes to customizing the components and determining how your users will interact with them. For example, you can create your own buttons or controls to accept input from users, and then use that input to change the appearance of data that shows in the component.
What you will learn in this article
This article shows you how to create a button on a Web page that turns on or off the display of a chart legend. Something like this would be useful when you don't want users to have access to the chart toolbar or Commands and Options dialog box, but you do want to give them some control over certain chart features.
After completing this example, you'll have experience creating an Office Chart component that uses data from an Access database; a better idea of how you can use scripting to control chart features; a glimpse of the Chart Component object model, which you can study in greater detail at your leisure; and a start on using the Microsoft Script Editor.
Before you begin
To follow the steps below, it is best to have a full installation of Microsoft Office Professional Edition 2003 or Microsoft Office XP Professional Special Edition. This example uses Microsoft Office FrontPage® 2003, the Office 2003 Web Components (if you are using Office XP, you will be using the Office XP Web Components), and the Northwind Traders sample database from Access for the chart's source data.
Intall the Office Web Components
Do one of the following:
Create and add script to show or hide a chart legend
The following example uses FrontPage to create a chart. You can also create a chart in Access, or publish an interactive worksheet in Microsoft Office Excel 2003.
First, in FrontPage, create a chart on a Web page using Access data.
- In FrontPage, point to New on the File menu, and then click Page or Web. In the New Page or Web pane, under New, click Blank Page.
- On the Insert menu, click Web Component, click Spreadsheets and charts, click Office Chart, and then click Finish.
- On the Data Source tab of the Commands and Options dialog box, select Data from a database table or query, and then click Connection.
- Click Edit, and in the Select Data Source dialog box, locate and select the Northwind sample database (Northwind.mdb) that comes with Access; then click Open.
Note In a default installation of Access 2003, the Northwind Traders database is located in the C:\Program Files\Microsoft Office\Office11\Samples folder – for Access 2002, C:\Program Files\Microsoft Office\Office10\Samples. If you have not started Access yet, Northwind.mdb may not yet be installed on your computer. In that case, start Access, click Help, point to Sample Databases, and then click Northwind Sample Database. Then quit Access.
For the purposes of this example, it's acceptable to connect to a database on your local computer. However, when you create a real-life solution such as this, be sure that the database is located on a shared server that your intended audience can access before you connect to it.
- In the Select Table dialog box, locate and select the Invoices table, and click OK.
- In the Commands and Options dialog box, select the Type tab, click Column, and then select the first column subtype in the list (called Clustered Column) on the right.
- Click Field List on the chart toolbar. You might need to move the Commands and Options dialog box to another area on the screen in order to see the Field List button.
- Click Salesperson in the list, and then click Add to to add the Salesperson field to the series area of the chart.
- Click Country in the list, click the arrow next to the Add to box, click Category Area, and then click Add to.
- Click UnitPrice in the list, click the arrow next to the Add to box, click Data Area, and then click Add to.
- In the chart, click the arrow on the Country field button, clear the (All) check box, and then select the USA check box.
Your chart should look like this:
- Click a blank area of the chart (outside the plot area, but within the borders of the chart), and then click the Show/Hide tab in the Commands and Options dialog box. Clear the Toolbar check box and the Field buttons/drop zones check box.
Next, add script to the page to show or hide the legend.
In this step, you'll create a button control that, when clicked, either shows or hides the legend. To do this, you have to write code that specifies what should happen to the chart object when the button is clicked.
- In FrontPage, on the Tools menu, point to Macro, and then click Microsoft Script Editor.
- In the Toolbox on the left, drag the Button control to the Web page in a location beneath the chart. (You might have to move the Script Editor window to do this.)
- On the Web page, double-click the button to display the Push Button Properties dialog box.
- In the Name box , type Btn1.
- In the Value/label box, type Show or Hide Legend, and then click OK.
- Return to the Script Editor by pressing ALT+TAB.
Note the script window in the middle of the screen. Within the code in that window should be an expandable line that looks like this (when collapsed):
<-- OBJECT id=ChartSpace1 -->
This represents the chart object you just created on the Web page. The chart has an entire object model made up of separate objects that represent the parts and features of the chart. To see the object models for each of the Office Web Components, locate the file Owcvba10.chm on your computer and double-click it.
Below the line that represents the chart object, you should see a line that looks like this:
<p><input type="button" value="Show or Hide Legend" name="Btn1"></p>
This line of code represents the button you just added to the page. Next you'll specify what should happen to the chart object when that button is clicked.
- At the top of the script window, click the arrow next to the Client Objects & Events list, and then click Btn1.
- In the box to the right, click onclick.
You should now have a script block and code for an onclick subroutine for Btn1 that looks like this:
<script id=clientEventHandlersVBS language=vbscript>
Next you'll insert the code for showing or hiding the legend between the Sub Btn1_onclick and the End Sub lines.
- Type the following code under Sub Btn1_onclick, exactly as shown here:
If ChartSpace1.Charts(0).HasLegend=True Then
Explanation of code When a user clicks the button (that's the on_click event for the button named Btn1), the code runs. Through a conditional IF statement, it is determined whether the first chart in the first chartspace has a legend. (In this example, there is only one chart in one chartspace on the page. You could have added multiple charts to the same chart space, in which case you could have specified ChartSpace1.Charts(1) to look at the second chart.) If it is true that the chart has a legend, the code specifies that the legend should be hidden. If it is false that the chart has a legend, the code specifies that the legend should be displayed.
The above code example is a classic example of an IF-THEN-ELSE structure, often used in coding. However, a simpler alternative would be to use the following single line of code, which performs the same action:
ChartSpace1.Charts(0).HasLegend = Not ChartSpace1.Charts(0).HasLegend
Finally, review and test the Web page.
- After adding script to the page, switch to the Web page by either clicking it if it's visible, or by pressing ALT+TAB.
- On the File menu, click Preview in Browser.
- Be sure the correct browser is selected in the Preview in Browser dialog box (the Office 2003 Web Components require Microsoft Internet Explorer 5.01 or later, Office XP Web Components require Internet Explorer 4.01 or later), and then click Preview.
- If you haven't already saved the Web page, do so when prompted.
- In the browser window, click Show or Hide Legend.
Your Web page should look something like this:
- You can find object model information and examples for each of the Office Web Components in the Help file Owcvba10.chm, which is installed with the Office Web Components. You can access the Help file in programming environments such as Microsoft Visual Basic® (when you create a reference to the Office Web Components) or by directly opening the file from your computer.
- You can find more in-depth information about scripting and programming Office Web Components, Office XP applications, and other Microsoft applications on the Microsoft Developer Network (MSDN) .
For more information about programming in Access, visit the Office Developer Center on the Microsoft Developer Network (MSDN).