Floating chart

[dtscfo]

Is there a way to "float" a chart on screen so when I scroll down through
data, the chart stays on screen?

 

[Jon Peltier]

You can show the Chart Window (on the chart's right click menu), but I think
all you need to do is touch anything else in the Excel window to make the
chart window vanish. You could open a modeless userform, and use either John
Walkenbach's (http://www.j-walk.com/ss/excel/tips/tip66.htm) or Stephen
Bullen's (http://oaltd.co.uk/DLCount/DLCount.asp?file=PastePicture.zip)
technique to put the chart onto the form.

- Jon

 

[Ken Johnson]

Is there a way to "float" a chart on screen so when I scroll down through
data, the chart stays on screen?

Hi,
This crude technique forces the chart to stick to an appropriate corner
of the most recently selected range of cells. If you scroll the chart
out of view it doesn't flow with the scrolling, however, it snaps into
place after a new range of cells has been selected. Its preferred
position is for the chart's top-left corner at the selections
bottom-right corner unless it can't fit there because of the edge of
the worksheet, then it moves to a different position that will not
squash the chart.

It would be very annoying if the chart continued to zap around while
you are working on the new area of the worksheet so I have included the
option of turning off the chart's selection chasing behaviour with a
Worksheet_BeforeDoubleClick Event Procedure, which toggles the
selection chasing behaviour. So, if you scroll to a new area and the
chart doesn't follow after a selection change then just double click
and make another selection change and it will snap into place. Then
double click again and the chart will not get in your way while you
work in the new area. ( the Worksheet_BeforeRightClick Event Procedure
could be just as easily used for the toggling of the selection chasing
behaviour)
Code follows...

Option Explicit
Public blnTagSelection As Boolean
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
blnTagSelection = Not blnTagSelection
End Sub

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
If blnTagSelection Then
Dim MyChart As Shape
Set MyChart = Me.Shapes("Chart 1")
If Selection.Left + Selection.Width _
+ MyChart.Width > Rows(1).Width Then
MyChart.Left = Selection.Left - _
MyChart.Width
Else: MyChart.Left = Selection.Left + _
Selection.Width
End If
If Selection.Top + Selection.Height _
+ MyChart.Height > Columns(1).Height Then
MyChart.Top = Selection.Top - MyChart.Height
Else: MyChart.Top = Selection.Top + _
Selection.Height
End If
MyChart.ZOrder msoBringToFront
End If
End Sub

To get the code into place and working..

1. Copy it

2. Right click the sheet tab of the worksheet with the chart, then
select "View Code" from the popup menu that appears. This will take you
to the worksheet's code module in the Visual basic Editor.

3. Paste the code into the blank code module.

4. Edit the line "Set MyChart = Me.Shapes("Chart 1")" so that your
chart's correct name is used in the brackets. You will see your
chart's correct name in the Name Box on the right side of the Formula
Bar when your chart is selected. Just make sure that the whole chart is
selected and not just the Chart area. To select the whole chart switch
to the selection arrow cursor and sweep around the chart so that the
selection marquee includes the whole chart.

5. Press Alt + F11 to return to Excel

6. For the code to work the workbook's Security level will need to be
no higher than Medium and when the workbook is opened the "Enable
Macros" button on the "Security Warning" dialog must be clicked.
If Security is too high then go Tools|Macro|Security... select
Medium|OK|Close|ReOpen that workbook|Click the "Enable Macros" button
on the "Security Warning" dialog.

Ken Johnson