Microsoft Office Tutorials and References
In Depth Information
Creating an Interactive Chart without VBA
The most interesting aspect of this application is that it uses no VBA macros. The interactivity is a
result of using Excel’s built-in features. The cities are chosen from a drop-down list, using Excel’s
Data Validation feature, and the data option is selected using four Option Button controls, which
are linked to a cell. The pieces are all connected using a few formulas.
This example demonstrates that it is indeed possible to create a user-friendly, interactive
application without the assistance of macros.
This workbook, named climate data.xlsx , is available on the companion CD-ROM.
The following sections describe the steps I took to set up this application.
Getting the data to create an interactive chart
I did a Web search and spent about five minutes locating the data I needed at the National
Climatic Data Center. I copied the data from my browser window, pasted it to an Excel
worksheet, and did a bit of clean-up work. The result was four 13-column tables of data, which I named
PrecipitationData , TemperatureData , SunshineData , and WindData . To keep the
interface as clean as possible, I put the data on a separate sheet (named Data).
Creating the Option Button controls for an interactive chart
I needed a way to allow the user to select the data to plot and decided to use OptionButton
controls from the Forms toolbar. Because option buttons work as a group, the four Option
Button controls are all linked to the same cell: cell O3. Cell O3, therefore, contains a value from 1
to 4, depending on which option button is selected.
I needed a way to obtain the name of the data table based on the numeric value in cell O3. The
solution was to write a formula (in cell O4) that uses Excel’s CHOOSE function:
Therefore, cell O4 displays the name of one of the four named data tables. I then did some cell
formatting behind the OptionButton controls to make them more visible.
Creating the city lists for the interactive chart
The next step is setting up the application: creating drop-down lists to enable the user to choose
the cities to be compared in the chart. Excel’s Data Validation feature makes creating a
dropdown list in a cell very easy. First, I did some cell merging to create a wider field. I merged cells
J11:M11 for the first city list and gave them the name City1 . I merged cells J13:M13 for the second
city list and gave them the name City2 .