Error Running Recorded Macro to Insert Map Object (135499)

SYMPTOMS

In Microsoft Excel, when you run a macro that you recorded while you inserted a map object on your worksheet, you may receive one of the following error messages:

Run-time error '1004':

DrawingObjects method of Worksheet class failed.

-or-

Run-time error '1000':

Range does not have writeable Width property

CAUSE

This problem occurs because when you turn on the macro recorder and insert a map object, the incorrect commands are recorded. If you do not record the action of entering the data on the worksheet and then creating the map from this data, you receive the run-time error '1004' when you run the macro because the recorded macro contains a line similar to the following:

   ActiveSheet.DrawingObjects("Marquee 0").Select

This line causes the error message because the drawing object Marquee does not exist when you run the macro.

If you do record the process of entering the data and then creating the map, you receive the run-time error '1000' when you run the macro because the recorded macro contains lines similar to the following:

   Selection.Width = 184.5
   Selection.Height = 156

When you run the macro, the first of these lines returns the error message because the Width property of the Range object is a Read Only property, and a range of cells is selected when this command runs.

RESOLUTION

To work around this problem, you can modify your recorded macro to appear similar to the following example:

   Sub Create_Map()
      ' Select the range on the worksheet that contains the
      ' data that you want to use to create the map
      Range("A1:B5").Select
      ActiveSheet.OLEObjects.Add(ClassType:="MSDataMap.1").Activate
   End Sub

Note that this procedure creates a map object on top of the data on the worksheet. If there are multiple maps available for the data that you use to create the map, the Multiple Maps Available dialog box appears when you run this macro, and you must select a map in order for the macro to continue.

Microsoft provides examples of Visual Basic 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. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

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.

REFERENCES

For more information about using the Visual Basic Width property, click the Index tab in the Microsoft Excel Help, type the following text

Width property

and then double-click the selected text to go to the "Width Property" topic.