XL97: Problems with Recorded Macro That Creates Map Object

• Microsoft Excel 97 for Windows

SYMPTOMS

When you record a macro that embeds a Microsoft Map object in a worksheet, the following problems may occur when you run the macro:

• The position and size of the map differs from the map you created when you recorded the macro.

  -and-

• You receive the following error message:

        Run-time error '1004':
        Method 'CutCopyMode' of Object '_Application' failed.
  

WORKAROUND

To workaround this problem, modify the macro to set the proper coordinates for the map object and remove the CutCopyMode method. The following steps illustrate how to record and modify a macro that embeds a Microsoft Map object on your worksheet.

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Recording the Macro

1. In Microsoft Excel, open a new workbook.

2. In cells A1:B2 on Sheet1, type the following:

         A1: Mexico     B1: 100
         A2: Canada     B2: 150
   
   

3. On the Tools menu, point to Macro, and click Record New Macro. Type "CreateMap" (without the quotation marks) in the "Macro name" box, and click OK to start recording.

4. Select cells A1:B2.

5. On the Insert menu, click Map. Draw the map object on the worksheet.

6. Select cell A1 on the worksheet to deactivate the map object.

7. On the Tools menu, point to Macro and click Stop Recording.

Modifying the Macro

1. On the Tools menu, point to Macro and click Macros. Select the Createmap macro and click Edit.

   The CreateMap macro appears in the Code window in the Visual Basic Editor. It will look similar to the following example:

         Sub CreateMap()
   
             Range("A1:B2").Select
             Range("B2").Activate
             ActiveSheet.OLEObjects.Add(ClassType:="MSMap.8", Link:=False, _
                 DisplayAsIcon:=False, Left:=3.75, Top:=9, Width:=3.75, _
                 Height:=9).Activate
             Application.CutCopyMode = False
             Range("A1").Select
         End Sub
   
   

2. Modify the macro such that the modified macro looks like the following example:

         Sub CreateMap()
   
             Range("A1:B2").Select
             Range("B2").Activate
             ActiveSheet.OLEObjects.Add(ClassType:="MSMap.8", Link:=False, _
                 DisplayAsIcon:=False, Left:=50, Top:=50, Width:=200, _
                 Height:=160).Activate
             Range("A1").Select
         End Sub
   
      Notice that you change the arguments for the Add method for the
      OLEObjects collection to indicate the coordinates and size of the
      data map. Additionally, you must remove the Application.CutCopyMode =
      False line.
   
   

3. On the File menu, click "Close and Return to Microsoft Excel".

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.