XL2000: Grouped Drawing Objects Run Separate Macros (213233)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q213233

SYMPTOMS

When you assign different macros to drawing objects, group two or more of the drawing objects on a worksheet, attempt to create a group macro, and then click an object in the group, Microsoft Excel runs the macro that is assigned to the object you clicked, as in the following example:
  1. Start Excel.
  2. Press ALT+F11 to start the Visual Basic editor.
  3. On the Insert menu, click Module.
  4. In the module sheet, type the following code:
    Sub Oval_Code()
   MsgBox "This is an oval."
End Sub

Sub Rectangle_Code()
   MsgBox "This is a rectangle."
End Sub

Sub Group_Code()
   MsgBox "This is a group."
End Sub
  5. Press ALT+F11 to return to Excel.
  6. On the Drawing toolbar, click Oval, and then draw an oval on the worksheet.

    NOTE: If the Drawing toolbar is not visible, point to Toolbars on the View menu, and then click Drawing.
  7. On the Drawing toolbar, click Rectangle, and then draw a rectangle on the worksheet.
  8. Right-click the oval, and then click Assign Macro on the shortcut menu.
  9. In the Assign Macro dialog box, click Oval_Code, and then click OK.
  10. Right-click the rectangle and then click Assign Macro on the shortcut menu.
  11. In the Assign Macro dialog box, click Rectangle_Code, and then click OK.
  12. Press and hold CTRL+SHIFT, click the rectangle, and then click the circle.

    Both objects are selected.
  13. On the Draw menu, click Group.
  14. Right-click either object to select the group, and then click Assign Macro on the shortcut menu.
  15. In the Assign Macro dialog box, click Group_Code, and then click OK.
  16. Click outside the group to unselect the group.
  17. Click the rectangle.

    Note that the macro assigned to the rectangle appears, not the macro assigned to the group.

CAUSE

This behavior occurs because you cannot assign a macro to a group of objects if the objects already are assigned macros. When you click an object that belongs to a group it will run any macro that is assigned to that specific object.

RESOLUTION

To resolve this issue, do not assign individual macros to objects that you plan to group. You can assign a macro to a group if the objects within the group are not already assigned macros, as in the following example:
  1. Start Excel.
  2. Press ALT+F11 to start the Visual Basic editor.
  3. On the Insert menu, point to Macro, and then click Macros.
  4. In the module sheet, type the following code: 
    Sub Group_Code()
   MsgBox "This is a group."
End Sub
  5. Press ALT+F11 to return to Excel.
  6. On the Drawing toolbar, click Oval, and then draw an oval on the worksheet.
  7. On the Drawing toolbar, click Rectangle, and then draw a rectangle on the worksheet.
  8. Press and hold SHIFT, click the rectangle, and then click the oval.

    Both objects are selected.
  9. On the Draw menu, click Group.
  10. Right-click either object to select the group, and then click Assign Macro on the shortcut menu.
  11. In the Assign Macro dialog box, click Group_Code, and then click OK.
  12. Click outside the group to unselect the group.
  13. Click the rectangle.

    Note that the group macro is called.

REFERENCES

For more information about grouping objects, click Microsoft Excel Help on the Help menu, type group drawing objects in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbprb kbProgramming KB213233
©2004 Microsoft Corporation. All rights reserved.