Clearly and Simply

Interactive Tooltip Examples

3 Examples of how to use an ActiveX Spreadsheet Control as a Tooltip on a Microsoft Excel Worksheet

The recent post described how to use an ActiveX Spreadsheet Control as a tooltip on Excel worksheets. At first sight this seemed to be a nifty little idea to spice up tooltips on Microsoft Excel worksheets. In the meantime, however, I had to learn that there is a major pitfall:

The heart of the solution, the ActiveX Spreadsheet Control is part of the so called Office Web Components. So far, so good. The problem is that Microsoft does not ship the Office Web Components with Office 2007 and later anymore. In other words, in a clean, new Office 2007/2010 installation, the ActiveX Spreadsheet Control is not available anymore.

Microsoft still provides the Office Web Components for free download (Office 2003 Add-in: Office Web Components), but there is still a major drawback: if you want to use the described technique, you have to make sure that every user of your workbook either uses Excel 2003 or has the Office Web Components installed.

At the end of the previous post I announced a follow up article with more practical examples using the technique. Knowing now what I didn’t know before, I pondered if this article would still make sense. To cut a long story short, I finally decided to publish it despite the limitation described above. Maybe some of you will still find it interesting and useful.

Today’s post provides 3 different use cases of how to take advantage of the ActiveX Spreadsheet Control in a tooltip:

• a scrollable tooltip
• side calculations within a tooltip
• an improved version of the Summary Card tooltip

As always the articles provides all Excel workbooks for free download.

Interactive Tooltips on Excel Worksheets

The Next Level of Tooltips on Excel Worksheets: Taking Advantage of the ActiveX Spreadsheet Control

Tooltips are one of the most helpful features when prospecting large data sets.

We already had a couple of articles talking about tooltips, on charts and on worksheets:

Tooltips on Microsoft Excel Tables

Charts in Excel Table Tooltips

Display all Fields of a Row in large Excel Tables

Better Chart Tooltips with Microsoft Excel 2010

Today’s post is trying to take this to the next level: an interactive tooltip on a Microsoft Excel worksheet. You may ask:

“What the heck is an interactive tooltip?”

What I am thinking of is a tooltip enabling you not only to read additional information but also interact with it. E.g. copy data from the tooltip and paste it somewhere else, do side calculations based on the currently selected data, browse through a longer list of data in the tooltip, etc.

Impossible? No it isn’t. Microsoft provides a very useful ActiveX control which enables us to do exactly this: the Microsoft Office Spreadsheet Control. Today’s post shows how to take advantage of this ActiveX control in order to provide an interactive tooltip on Excel worksheets - as always including the Excel workbook for free download.

Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard

Bluffing the “Beer Prices at Oktoberfest” Tableau Dashboard with Microsoft Excel using Roll Over Tooltips and Web Actions

Recently we had an article visualizing the beer prices and beer price development at Oktoberfest with a Tableau dashboard: O'zapft Is!

Oktoberfest is long over already and there is peace in the valley of Munich again. Having said that, I would like to come back to this visualization once more. We had a couple of posts here on Clearly and Simply showing how to bluff Tableau’s great interactive features with Microsoft Excel:

Bluffing Tableau Actions with Microsoft Excel

Bluffing a Visual Cross-tab with Excel

The Next Level of Interactive Microsoft Excel Dashboards

Microsoft Excel Site Catchment Analysis

Drawing on this tradition, today’s article presents an Excel workbook emulating the Tableau Oktoberfest Dashboard. The technique behind this bluff is a very clever approach my blogging colleague Jordan Goldmeier published in several articles over at his Option Explicit VBA Blog.

Stealing Jordan’s idea, today’s post shows how to recreate the Oktoberfest Beer Price Tableau dashboard with Microsoft Excel, as always including the Excel workbook for free download.

Filter Excel Tables by Double Clicking

How to set the active cell’s value as the filter criteria in Microsoft Excel Tables by double clicking

In a post on the Excel Hero LinkedIn Group Charles asked today:

“[…] I wish to click on a cell with a purchase order number and have that action filter the list based on that purchase order number.
What could be a strategy please? […]”

In one of the answers to Charles’ question, Craig pointed to the most obvious solution: right click and select Filter | Filter By Selected Cell’s Value. Well, I know this is available in Excel 2010, but I can’t tell you no lies: I don’t even remember if this feature was available already in 2003 and earlier.

Anyway. Although at least Excel 2010 provides a built-in feature for filtering by the value of the active cell, Charles' idea is very interesting and reminded me of a post we had here last year: Sort Excel Tables by Double Clicking.

The article provided a small VBA code snippet to sort data tables by double clicking into any given column. The technique used back then for sorting can easily be adjusted to implement Charles’ original idea: filter data tables by the value of the active cell after double clicking and remove the filter criteria with another double click.

The implementation is simple: all you need is to define a name for the range where your data is (e.g. “myData”) and a very small piece of VBA code (24 lines of code) in the event driven Worksheet_BeforeDoubleClick event procedure.

The code

• checks if the the user double clicked inside of the data
• detects the active column
• checks if the auto filter is set and sets it, if not
• checks if a criteria is defined already and if so, removes the criteria or otherwise sets the criteria to the value of active cell

If you are interested in the details, have a look at the workbook provided for download below. If you have any questions, please leave me a comment.

A simple and effective way of setting / removing filter criteria by double clicking. The technique is not limited to one column. You can also select several columns to filter your data.

Here is the workbook for free download:

Download Filter Excel Tables by Double Clicking (Microsoft Excel 2003, 93.5K)

You can even combine the filtering by double clicking with the sorting by double clicking technique, if you change the VBA to sort only if the user double clicks on the header of a column. A simple IF clause and a combination of the two code snippets will do the job.

Stay tuned.