XL: Control Method of Sheet Class Failed with Grouped Control

The information in this article applies to:

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In a Visual Basic module in Microsoft Excel, when you use the Value property to set the property of a control, or return the property of a control contained on a worksheet or dialog sheet, you receive the following error message if the control is grouped

   Run-time error '1004':

   <controlname> method of Worksheet class failed

Note that controlname in the error message may also be DrawingObjects, if you use the DrawingObjects method to return the control object.

CAUSE

This behavior is by design in Microsoft Excel. When you select multiple controls, and group them, the controls are now treated as a single object. This makes it easier to move the controls, because they move together, but you can no longer modify the properties of an individual control.

Note that when you select a grouped object, such as a check box, and choose Object from the Format menu, there is no Control tab to set the value of the check box to checked or mixed.

WORKAROUND

To avoid this behavior, you must ungroup the control before you can set the properties of an individual control, such as setting the value of an Option Button to selected, or the value of a Check Box to clear (unchecked).

MORE INFORMATION

For more information about verifying the value of a check box, query on the following words in the Microsoft Knowledge Base:

   value and check and box and xlon

REFERENCES

"Visual Basic User' Guide," version 5.0, Chapter 11, "Controls and Dialog Boxes"

For more information about the Value Property, choose the Search button in the Visual Basic Reference and type:

   Value Property