MapForums

Public Sub MapIt(ws As worksheet, rcsutid as string)
Dim oMap As MapPoint.Map
Dim objPin As MapPoint.Pushpin
Dim szconn As String
Dim oDS As MapPoint.DataSet
Dim obj As OLEObject
Dim lRangeValues(1 To 3) As Variant
Dim zRangeNames(1 To 3) As String
Dim RS As MapPoint.Recordset
Dim High As Integer
Dim oDM As MapPoint.DataMap
 
On Error GoTo ErrorHandler
'make map interactive
ws.OLEObjects(1).Activate
'Get map
Set oMap = GetObject(, "MapPoint.Application").ActiveMap
'Clean out old datasets
For Each oDS In oMap.DataSets
oDS.Delete
Next
'Create new datasets
With oMap.DataSets
szconn = ActiveWorkbook.Path & "\JI_Dealer_Business_Plan.xls!" & ws.Name & "!location"
Set oDS = .ImportData(szconn, , geoCountryUnitedStates, , geoImportExcelNamedRange)
oDS.DisplayPushpinMap
Set RS = oDS.QueryAllRecords
RS.MoveFirst
Set objPin = RS.Pushpin
objPin.Location.Goto
oMap.ZoomIn
objPin.Symbol = 291
oDS.Name = rcustid & "Dealer Location"
szconn = ActiveWorkbook.Path & "\JI_Dealer_Business_Plan.xls!" & ws.Name & "!counties"
Set oDS = .ImportData(szconn, , geoCountryUnitedStates, , geoImportExcelNamedRange)
Set RS = oDS.QueryAllRecords
RS.MoveFirst
High = 0
Do Until RS.EOF
If High < CInt(RS.Fields("I | K | D").Value) Then
High = CInt(RS.Fields("I | K | D").Value)
End If
RS.MoveNext
Loop
lRangeValues(1) = 0
lRangeValues(3) = High
zRangeNames(1) = "Low 0"
zRangeNames(2) = "Medium " & High / 2
zRangeNames(3) = "High " & High
Set oDM = oDS.DisplayDataMap( _
geoDataMapTypeShadedArea, _
, , , geoRangeTypeContinuous, geoRangeOrderHighToLow, 13, _
, lRangeValues, zRangeNames)
oDM.LegendTitle = "Sales By County for " & rcustid
oDS.Name = "Sales By County"
End With
'Change map to Data Map
oMap.MapStyle = geoMapStyleData
'Inactivate map
ws.Range("a1").Select
GoTo Done
ErrorHandler:
MsgBox Err.Description
Done:
End Sub

// first create it etc...
// Name of the control here is MP
// Owner is the control where the control is sitting on, eg the sheet

MP.BeginInit();
Owner.Controls.Add(MP);
MP.EndInit();
MP.Visible = true;

// I'm not sure on the sequence for the BeginInit :(
// To move it:
// Owner 2 is the second form / sheet

MP.Visible = false;
Owner.Controls.Remove(MP);
Owner2.Controls.Add(MP);
MP.Visible = true;

// Again I'm not sure what to do with begin/end Init()