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

Did you use a control from the control toolbox toolbar.

You can see excel's problem by starting a new workbook, dropping a commandbutton
from the control toolbox toolbar on a sheet--make the code as simple as a
msgbox.

Window|new window
window|arrange|horizontal

The bottom window's button works fine--the top window's button not so good.

One fix is to not use multiple windows <bg.

Or maybe you could use a button from the forms toolbar and assign your code to
that button. (I'd bet a few changes would be required.)



david wrote:

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

--

Dave Peterson

Dave,

Thanks for the reply.

I've seen and dealt with the problem you describe previously. The problem I
am having occurs even after the changes you mention.

Funnily enough, I have just seen that my problem occurs on my computer, but
on the main user's computer (not mine) it does not. However, if I try to
recreate the problem on another colleague's computer, the error pops up
again. So,

1) my computer = problem occurs
2) colleagues computer = problem occurs
3) main user's computer = no problem.

Strange - I am going have to start comparing machines, maybe this problem
has something to do with settings/properties/installed versions of software
on the different computers.

Cheers for the feedback.

David (Perth, Australia)

The problem occurs if you use a button from the Forms toolbar?

david wrote:

Dave,

Thanks for the reply.

I've seen and dealt with the problem you describe previously. The problem I
am having occurs even after the changes you mention.

Funnily enough, I have just seen that my problem occurs on my computer, but
on the main user's computer (not mine) it does not. However, if I try to
recreate the problem on another colleague's computer, the error pops up
again. So,

1) my computer = problem occurs
2) colleagues computer = problem occurs
3) main user's computer = no problem.

Strange - I am going have to start comparing machines, maybe this problem
has something to do with settings/properties/installed versions of software
on the different computers.

Cheers for the feedback.

David (Perth, Australia)

--

Dave Peterson

Hi Dave,

Yes it does, however, the problem is not related to clicking buttons as such.

The gantt chart I speak of is dynamically recreated (destroyed and re-built)
each time the worksheet it is on is activated. This all works fine when only
one excel window is open. However, when two windows are open, any action that
causes the gantt chart worksheet to be reactivated can cause the error I
mentioned in my first post. The 'refresh' code I mentioned previously is just
one example of code which causes the gantt chart's worksheet to be activated.
However, the error does not occur on a regular basis - only sometimes (seems
to be at random)

I have been in touch with Microsoft Technical Support who are investigating
my problem at this current point time. Hopefully they will be able to shed
some light on what is going wrong.

I will try and post the resolution up if the problem gets solved.

Cheers all - especially dave

DAvid

Ahh.

I don't have a guess.

Please post back with the info you get from MS. Then Google will have it for
the next person.

Good luck,

david wrote:

Hi Dave,

Yes it does, however, the problem is not related to clicking buttons as such.

The gantt chart I speak of is dynamically recreated (destroyed and re-built)
each time the worksheet it is on is activated. This all works fine when only
one excel window is open. However, when two windows are open, any action that
causes the gantt chart worksheet to be reactivated can cause the error I
mentioned in my first post. The 'refresh' code I mentioned previously is just
one example of code which causes the gantt chart's worksheet to be activated.
However, the error does not occur on a regular basis - only sometimes (seems
to be at random)

I have been in touch with Microsoft Technical Support who are investigating
my problem at this current point time. Hopefully they will be able to shed
some light on what is going wrong.

I will try and post the resolution up if the problem gets solved.

Cheers all - especially dave

DAvid

--

Dave Peterson