I
ian.mansfeld
Excel 2003: I have found this problem with an embedded chart into
which I copy a map. I then use the Chart Events to track the mouse
cursor and 'drop' waypoints onto the map (making this work is another
topic on it's own!). However, if the chart is wider than the screen
window then as it is scrolled left a point is reached where these
Autoshapes disappear. By trial and error, I have found that they can
be made to re-appear by first selecting a sheet cell then selecting
the chart following every scroll. The problem seems to be linked to
the 'original' RHS boundary of the Window, and any shapes right of
this line will disappear as the window scrolls left. I have also found
that the Chart ToolTips (the tags that say Chart Area or Plot Area
etc. also vanish at around this point.
The problem is repeatable in that it happens on different PCs under
different OS (even in Excel for Mac under OS X). If you want to try
it, then open a Workbook and put a chart on the sheet (any chart with
any data or even no data at all will do). Now select the chart and
draw some shapes on it and evenly distribute them across the chart.
Make sure that you have at least one on the far RHS. To make sure that
the shapes are on the Chart and not the Sheet, grab them and try and
drag them off the Chart - if they are correctly placed you will not be
able to move them past the Chart edges. Now grab the chart RHS handle
and drag it until it is wider than the Window. Use the scrollbars to
move around and you will see that some shapes on the RH side of the
chart are gone. Exactly which ones depends on the width of your Chart
and Window. To see them again, click in a cell and then select the
Chart. Scroll again and it's gone. Repeat until bored.
An extensive search of archive Forums shows that this effect has been
seen by others, as far back as xL97. However, the answers show that
the readers have almost always misunderstood the problem. The most
frequent responses are to a) ensure the Autoshapes are on the Chart
not the Sheet, and b) to group all the shapes. Neither of these will
work, nor will any attempts to change the drawing Order.
I can offer a solution. It's not a real solution derived from
understanding the problem, but more of an engineers work-around (and I
am an Engineer, though in aerospace not software). The approach I have
adopted is to try and scroll using a procedure or event into which
code can be embedded to emulate manually re-selecting the sheet and
chart.
Having tried several methods involving the cursor that all failed, the
one I have made work uses the OnKey procedure. API calls could
probably be used too, but I want this code to run on both a PC and a
Mac.
The code below only deals with a Left scroll but you can probably
figure out how to do Right, Up and Down.
In the Worksheet module as a Worksheet Activate event
'activate the Application Object Window - must be first
else scrollbars will not clear every time
Windows(ActiveWorkbook.Name).Activate
'hide the cursor scrollbars
ActiveWindow.DisplayVerticalScrollBar = False
ActiveWindow.DisplayHorizontalScrollBar = False
're-define the left arrow key to cause a small scroll
Application.OnKey "{LEFT}", "LeftShift" LeftShift code in
a Module
'activate the chart ready for use or bypass step if no
chart on sheet
On Error Resume Next
ActiveSheet.ChartObjects(1).Activate
In the Worksheet module as a Worksheet Deactivate event
'activate the Application Object Window
Windows(ActiveWorkbook.Name).Activate
'put back the cursor scrollbars
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
'put the left arrow key back as it was
Application.OnKey "{LEFT}"
Then in a module as a Procedure
Sub LeftShift()
'inhibit screen updating (does not make much difference)
Application.ScreenUpdating = False
'set Application Window active before scrolling
Windows(ActiveWorkbook.Name).Activate
'move stuff left by a little
ActiveWindow.SmallScroll ToRight:=-1
'activate first chart (one chart per sheet expected) ignore
if no chart found on sheet
On Error Resume Next
ActiveSheet.ChartObjects(1).Activate
'reactivate screen updating
Application.ScreenUpdating = True
End Sub
When you select this Sheet only, the action of the arrow key will
change. It will now cause scrolling and the required calls to ensure
that the Shapes are still visible after the scroll. As a clue, it will
get rid of the normal scrollbars so the key is the only way to scroll.
When you select another Sheet, all these changes are put back so that
the scrollbars re-appear and the arrow key behaves normally again.
Limitation: After scrolling, it is always Chart1 on the sheet that is
left activated.
Tip: Use OnKey for ALT+Left to call a procedure that moves 10 or more
cells at a time for faster scrolling emulating a Page Left.
Tip: .For uniform scrolling, set the underlying cells in the sheet to
be small and square. If you are not using the worksheet cells for
data, then the following code added to the WorkSheet Activate code
will do it: The Chart has to be unlocked from the cells first if it is
not to be distorted by this action.
Dim RowNo As Integer, ColNo As Integer
'If there is a Chart 1 then unlock it from cells
On Error Resume Next
ActiveSheet.ChartObjects(1).Placement = xlFreeFloating
'disable screen updating so that it happens faster and
without screen flicker
Application.ScreenUpdating = False
'fix the rows and columns to be equal size
For RowNo = 1 To 255 'max 65538 but 255 is Ok for an
A3 portrait chart
Rows(RowNo).RowHeight = 7.5 'ht in points
Next RowNo
'Columns are in character width, smallest unit =1 is approx
7.5 points
For ColNo = 1 To 255 'max 255 which is Ok for an A3
Landscape chart
Columns(ColNo).ColumnWidth = 1
Next ColNo
Application.ScreenUpdating = True
If anyone can provide a better fix for this problem I would be glad to
use it to replace my efforts.
V8R
which I copy a map. I then use the Chart Events to track the mouse
cursor and 'drop' waypoints onto the map (making this work is another
topic on it's own!). However, if the chart is wider than the screen
window then as it is scrolled left a point is reached where these
Autoshapes disappear. By trial and error, I have found that they can
be made to re-appear by first selecting a sheet cell then selecting
the chart following every scroll. The problem seems to be linked to
the 'original' RHS boundary of the Window, and any shapes right of
this line will disappear as the window scrolls left. I have also found
that the Chart ToolTips (the tags that say Chart Area or Plot Area
etc. also vanish at around this point.
The problem is repeatable in that it happens on different PCs under
different OS (even in Excel for Mac under OS X). If you want to try
it, then open a Workbook and put a chart on the sheet (any chart with
any data or even no data at all will do). Now select the chart and
draw some shapes on it and evenly distribute them across the chart.
Make sure that you have at least one on the far RHS. To make sure that
the shapes are on the Chart and not the Sheet, grab them and try and
drag them off the Chart - if they are correctly placed you will not be
able to move them past the Chart edges. Now grab the chart RHS handle
and drag it until it is wider than the Window. Use the scrollbars to
move around and you will see that some shapes on the RH side of the
chart are gone. Exactly which ones depends on the width of your Chart
and Window. To see them again, click in a cell and then select the
Chart. Scroll again and it's gone. Repeat until bored.
An extensive search of archive Forums shows that this effect has been
seen by others, as far back as xL97. However, the answers show that
the readers have almost always misunderstood the problem. The most
frequent responses are to a) ensure the Autoshapes are on the Chart
not the Sheet, and b) to group all the shapes. Neither of these will
work, nor will any attempts to change the drawing Order.
I can offer a solution. It's not a real solution derived from
understanding the problem, but more of an engineers work-around (and I
am an Engineer, though in aerospace not software). The approach I have
adopted is to try and scroll using a procedure or event into which
code can be embedded to emulate manually re-selecting the sheet and
chart.
Having tried several methods involving the cursor that all failed, the
one I have made work uses the OnKey procedure. API calls could
probably be used too, but I want this code to run on both a PC and a
Mac.
The code below only deals with a Left scroll but you can probably
figure out how to do Right, Up and Down.
In the Worksheet module as a Worksheet Activate event
'activate the Application Object Window - must be first
else scrollbars will not clear every time
Windows(ActiveWorkbook.Name).Activate
'hide the cursor scrollbars
ActiveWindow.DisplayVerticalScrollBar = False
ActiveWindow.DisplayHorizontalScrollBar = False
're-define the left arrow key to cause a small scroll
Application.OnKey "{LEFT}", "LeftShift" LeftShift code in
a Module
'activate the chart ready for use or bypass step if no
chart on sheet
On Error Resume Next
ActiveSheet.ChartObjects(1).Activate
In the Worksheet module as a Worksheet Deactivate event
'activate the Application Object Window
Windows(ActiveWorkbook.Name).Activate
'put back the cursor scrollbars
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
'put the left arrow key back as it was
Application.OnKey "{LEFT}"
Then in a module as a Procedure
Sub LeftShift()
'inhibit screen updating (does not make much difference)
Application.ScreenUpdating = False
'set Application Window active before scrolling
Windows(ActiveWorkbook.Name).Activate
'move stuff left by a little
ActiveWindow.SmallScroll ToRight:=-1
'activate first chart (one chart per sheet expected) ignore
if no chart found on sheet
On Error Resume Next
ActiveSheet.ChartObjects(1).Activate
'reactivate screen updating
Application.ScreenUpdating = True
End Sub
When you select this Sheet only, the action of the arrow key will
change. It will now cause scrolling and the required calls to ensure
that the Shapes are still visible after the scroll. As a clue, it will
get rid of the normal scrollbars so the key is the only way to scroll.
When you select another Sheet, all these changes are put back so that
the scrollbars re-appear and the arrow key behaves normally again.
Limitation: After scrolling, it is always Chart1 on the sheet that is
left activated.
Tip: Use OnKey for ALT+Left to call a procedure that moves 10 or more
cells at a time for faster scrolling emulating a Page Left.
Tip: .For uniform scrolling, set the underlying cells in the sheet to
be small and square. If you are not using the worksheet cells for
data, then the following code added to the WorkSheet Activate code
will do it: The Chart has to be unlocked from the cells first if it is
not to be distorted by this action.
Dim RowNo As Integer, ColNo As Integer
'If there is a Chart 1 then unlock it from cells
On Error Resume Next
ActiveSheet.ChartObjects(1).Placement = xlFreeFloating
'disable screen updating so that it happens faster and
without screen flicker
Application.ScreenUpdating = False
'fix the rows and columns to be equal size
For RowNo = 1 To 255 'max 65538 but 255 is Ok for an
A3 portrait chart
Rows(RowNo).RowHeight = 7.5 'ht in points
Next RowNo
'Columns are in character width, smallest unit =1 is approx
7.5 points
For ColNo = 1 To 255 'max 255 which is Ok for an A3
Landscape chart
Columns(ColNo).ColumnWidth = 1
Next ColNo
Application.ScreenUpdating = True
If anyone can provide a better fix for this problem I would be glad to
use it to replace my efforts.
V8R