excel loses functionality when a Map Point Active X Control is accessed through VBA

From: ruyasan (ruyasan.16gz0q_at_excelforum-nospam.com)
Date: 05/18/04 

Date: Tue, 18 May 2004 15:49:21 -0500

I'm having a lot of trouble finding a solution to my problem because
even though there are great resources around for Excel programing and
Map Point programing, there is almost nothing about useing them
together.

Having said that, I assume that what I describe below happens with any
complex Active X control so perhaps somebody here can help me.

Basically I have a map point control embedded into excel. The problem
is that once the controll is accessed in any way from VBA, excel seems
to "lose focus" and a lot of its features become unavailable.

Its a little hard to explain, but I shall try to illustrate what is
happeing:

1. I open up my spread*** (with the embedded MPControl) and excel is
fully functional

2. I hit a button which uses the map point control to calculate a map
point route (for those unfamiliar with map point - all that matters
here is that I accessed an Active X Control from VBA in this step)

3. Excel is no longer fully functional. I can still edit the contents
of cells but nothing much else works (for example, Validation and
Format Cell are grayed out in all the menues - as well as any other
functions which affect the way cells work)

4. I found out that if I select something within the embedded controll
(like a city name) and then select something back inside the
spread*** (like some cell) then everything returns to normal

The problem is that I cannot replicate step 4 programatically.

For instance, I have some code that does this at the end of my route
calculation function:

locPickUp.Select
wksDispatch.Range("A1").Select

This does not return excel to normal functionality (even though if I
physically select something in the MPControl and then a cell everything
is back to normal). This is a BIG problem for me because I have a
function which ads special validation requirements to a cell based on
data returned from the MPControl. Since the validation functionality of
excel turns off as soon as the map point control is accessed, the
program crashed with the message:

"Automation Error:
The object involved has disconnected from its clients."

It seems as though as soon as objects within the map point control are
accessed, a bunch of objects within excel's object tree disapear for
that while. So, how do I make them come back?

I'm really REALLY stuck on this - so any help would be infinately
apretiated! 

---
Message posted from http://www.ExcelForum.com/