Simple VBA question

[Sidereus]

Hello all,

I am sorry for posting to something that may have been discussed but I could not find the answer through a search.

I have a small macro that I am running from excel that loads address data that is stored on my worksheet into microsoft mappoint.

The code works but the problem is that when the macro is done copying the information over, it stops which then causes mappoint to close, making me unable to look at the data that I just transferred over on the map.

This same macro however when run from a command button, does not close mappoint.

I can only assume that the behaviour of the macro is affected but it being a command button (user click) versus a straight coded macro that can be run from a keyboard shortcut.

Is there a way to put a pause, or a stop on my non-command button version so that it won't close mappoint right away? Obviously the point of the macro is to allow someone to manipulate the data after the fact in mappoint.

If you need any additional information, please let me know.

 

[Smitty]

Welcome to the Board!

You'll need to post the code.

 

[Sidereus]

The code below does what I need it to do, but as soon as it inputs the last entry and ends the while loop, it then wants to shut down mappoint before I can use my map data, and this is what I am trying to prevent.

This same code under a command button that a user clicks to start, does not cause mappoint to close.




Sub DeliveriestoMapPoint()

Set App = CreateObject("MapPoint.Application.NA.16")
App.Visible = True
Set objMap = App.NewMap
Set objRoute = objMap.ActiveRoute

Row = 2
While Cells(Row, 1) <> " " // Yes there is a space in quotes
objRoute.Waypoints.Add objMap.FindAddressResults( _
Cells(Row, 1), _
Cells(Row, 2), , _
Cells(Row, 3), _
Cells(Row, 4), _
Cells(Row, 5)).Item(1)
Row = Row + 1
Wend

End Sub

 

[Sidereus]

Solved.

For those who might be interested.

2 ways.

1. Add at the top under app.visible the line App.UserControl = True

This stops MapPoint from closing

2. Use the MsgBox option.

At the end, right before End Sub add MsgBox("Click ok to close mappoint and return to excel")

 

[Smitty]

Glad you got it working. Sorry I didn't get back yesterday, but my PC had a meltdown and I didn't get back up until this morning.