Hi all,

I am working on a large Excel 2003 application. The primary worksheet used
is actually a gantt chart that is dynamically created each time the worksheet
is activated (i.e. it is blown away and rebuilt). The 'activate' sub for this
worksheet simply calls a sub called 'placebar' which does all the work. The
gantt chart is made up of Drawing objects (rectangles), each of which is
linked to a particular event. So, whenever the user clicks on a rectangle in
the gantt chart (an event), the app changes to a different worksheet that
displays the details of that event.

The application also has a button which performs the same action as Window
-New Window. By clicking this button, code is run which splits the current
worksheet into two windows which can be operated independently. Thus, with
two monitors, the user can have a different worksheet on each screen at the
same time.

There seems to be a one way dependency problem between the two screens. For
example, if the gantt chart is open on both screens and the user selects an
event from the gantt in window 1, code is run and a different worksheet is
activated in window 1 to display the details of the event. Throughout this
process, the gantt chart is always shown in window 2 and never changes. This
is what we want (Actions performed in one window should not affect the other
one). However, if the user has the gantt chart open in both windows and
selects an event from the gantt in window 2, the code that runs changes both
windows to a different worksheet and displays the details of the event i.e.
the details of the event are displayed in both window 1 and 2. This is not
what I want - selecting an event in window 2 should cause the event details
to be displayed in window 2 only, leaving the gantt chart displayed in window
1. It does not matter what worksheet is shown in window 1 originally, all
that matters is that window 1 should not be affected by actions peformed in
window 2.

So, I implemented some code which performs the following action:
- if the user has selected an event from the gantt in window 2, my code
saves the
name of the worksheet currently active in window 1.
- the code to display event details in window 2 runs (which as mentioned
above
also affects the worksheet displayed in window 1).
- my code then runs to reset window 1 to the worksheet that was previously
shown.

So, in affect, the first window still gets changed to a different worksheet
during the process of displaying an event, but my code resets window 1 to its
original worksheet.

This all works ok generally. However, my problem is as follows:

If the user has the gantt chart open in both windows, my refresh code only
works sometimes. For example, if the user has the gantt open in both windows
and clicks an event in window 2, code runs to display the event details and
eventually my 'refresh' code exectutes to re-activate the gantt worksheet in
window 1. My 'refresh' code simply activates the gantt worksheet which then
calls 'placebar' to recreate the chart. This only works sometimes. Sometimes
it works fine. Other times, it gets about halfway through execution of
'placebar' and raises an error on the 'Delete' call shown below.

' Delete all shapes
sch.Shapes.SelectAll
Selection.Delete

Error is: "method Delete of 'DrawingObjects' failed.

It seems to actually peform the deletion (the objects dissappear) but
execution cannot continue. This is a fatal error which forces the user to
close and re-open the workbook.

The 'placebar' sub generally works well and never suffered this problem
before I implemented code to 'refresh' window 1. I have even managed to save
the workbook in a state that when I open it, I can immediately click on an
event on the gantt in window 2 and the error occurs. However, if I open the
workbook, click on a different worksheet (not the gantt sheet) in window 2,
go back to the gantt (re-activate it) and then click on an event on the gantt
in window 2, the error does NOT occur. It does not matter which of the other
worksheets I activate first and no, the other worksheets do not contain any
'activation' code that would alter things...

My thinking is that by having two windows open, Excel is getting confused
somehow

Also, sometimes code execution manages to get past the 'delete' call shown
above but then fails further in the execution of 'placebar'. For example,
other errors I have seen a

"Automation Error: The object invoked has disconnected from its clients"

which occurs on line:

sch.Shapes.AddLine(xaxis, yaxis, xaxis, laxis).Select

and also:

"Automation Error: The callee (Server [not server app]) is not available and
disappeared; all connections are invalid. The call may have executed."

I still a newbie at VBA but from what I can see the code in 'placebar' has
nothing to do with Automation.

Any ideas anybody?? Thanks - this problem has b een a real pain.

Cheers,

David