Sorry for all the questions but it seems there isn't much literature on-line about embedding Map Point into excel, so this is the only place I know of to come for help. I tried searching these forums as well but it looks like nobody ran into my problem so far.


Basically I have a map point controll 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 spreadsheet (with the embedded MPControl) and excel is fully functional
2. I hit a button which calculates a route based on information in some cells. The route is calculated and displayed in the embedded control.
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 spreadsheet (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. This is a BIG problem for me because I have a function which ads special validation requirements to a cell based on a route look-up. 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."


Now, I am not TOO familiar with the inner workings of MS Office objects, but I'm guessing that while the map point controll is accessed, the object tree for excel is temporarily turned off so that the Map Point object tree can be used instead. So how would I go about letting VBA know that I would like to turn the excel tree back on?

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

if it helps, I noticed that excel exhibits similar behavior when one clicks on ANY active X control (such as a plain old command button)

however, with something like a command button, doing a simple Cells(1,1).Select returns excel to its normal functionality

this doesn't work with the MP Control

in other words - it seems that what happens is excel assigns the MPControl focus in a way different from what the Select method provides

I still don't know how to address this though

Could anyone at least acknowledge that what I'm writing makes any sesnse at all? Could this be a bug? Can anyone help me in any way what so ever? I've alreay wasted 2 days on this bloody glitch, so ANY suggestion would be apretiated!

for anyone who got here by way of google, the solution turns out to be to select the worksheet explicitly first, then some cells within it

for example:

myMapPointControl.NewMap geoNorthAmerica
Worksheets(1).Select
Worksheets(1).Range("A1").Select

will let you access the MPControl and then restore excel to status quo