"Pop-up" charts activated on hover

[hojikuru]

I'd like to have charts pop up when the user hovers over the source
data.

For instance, I have several sections of sales data in this form:

Jan Feb Mar Q1 Apr May ...

US
Target 5 6 ...
Actual 6 5
Forecast 5.5 5

Europe
Target 4 5 ...
Actual 4 5
Forecast 5 6

So, when the user hovers over the US or Europe "sections," which would
be the label row plus three following rows (tgt, act, fcst), a chart
of the data pops up to illustrate the numbers. I don't want to take
up room permanently displaying the charts, but want them accessible.

All help appreciated.

Michael Lambert
(e-mail address removed)

 

[Debra Dalgleish]

You could create a chart, and export it as a graphic:

'=====================
Sub ExportChartGIF()
ActiveChart.Export Filename:="C:\Data\ExpChart2K.gif", _
FilterName:="GIF"
End Sub
'========================

Then, insert the chart in a comment in the section heading cell. There
are instructions here:

http://www.contextures.com/xlcomments02.html#Picture

 

[Tushar Mehta]

That's a nice idea. Unfortunately, XL doesn't provide any way to find
the position of a moving/hovering mouse pointer -- at least, not while
the pointer is over a worksheet.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

Nice alternative to something that is otherwise not possible -- at
least not with a hover capability.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

Here's a solution that is close but does not rely on the mouse
position. Insted it requires that you click in a cell in the region of
interest.

Create the charts corresponding to the different regions. Shift+click
each to select the chartobject container (the selection rectangle
around the chart should have circles at the corners rather than black
filled squares) and name it (click in the name box at the extreme left
of the formula bar and start typing) with the name of the geographic
region (usa, europe, etc.)

Create named ranges with Insert | Name > Define... Select the range
that contains the data for each region and give it the *same* name as
that of the corresponding chartobject. [Note that you cannot use the
name box for this process since XL will select the named chartobject
rather than create a name for the range.]

Put the following code in a standard module:
Option Explicit
Public AutomateCharts As Boolean
Sub showAllCharts()
Dim I As Integer
AutomateCharts = False
With ActiveSheet.ChartObjects
For I = 1 To .Count
.Item(I).Visible = True
Next I
End With
End Sub
Sub startAutomateCharts()
AutomateCharts = True
End Sub

Put the following code in the ThisWorkbook module:
Option Explicit

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Range)
Dim I As Integer
If Not AutomateCharts Then Exit Sub '<<<<<
With Sh.ChartObjects
For I = 1 To .Count
.Item(I).Visible = False
Next I
End With
With Sh.Parent.Names
For I = 1 To .Count
If Intersect(Target, Sh.Range(.Item(I).RefersTo)) _
Is Nothing Then
Else
Sh.ChartObjects(.Item(I).Name).Visible = True
Exit For
End If
Next I
End With
End Sub

Once you run the startAutomateCharts procedure, a chart will pop up
when you click any cell in a specified region. So, click any cell in
the range named europe and the chart named europe will become visible.

Run the showAllCharts procedure to make all charts visible and to stop
the automation effect. This will be the only way to edit a chart.

Note that there is no code in the above procedures to protect against
developer/user/data errors...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Ed Ferrero]

Hi Michael,

A similar solution to that proposed by Tushar Mehta can be found at
http://edferrero.m6.net/Content/ChartSelector.zip

This uses the sheet selection change event, but takes the approach
of changing the chart series rather than changing the visibility
of many pre-determined charts.

Ed Ferrero
http://edferrero.m6.net