XL97: Run-Time Error 438 If Macro Uses ControlFormat Object

Last reviewed: January 9, 1998
Article ID: Q171075
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, if you run a Visual Basic for Applications macro that attempts to set the properties of a control in a worksheet, you may receive the following error message:

   Run-time error '438':
   Object doesn't support this property or method

CAUSE

This problem occurs if the macro attempts to use the ControlFormat object to set the properties of an ActiveX control. For example, if you create an ActiveX control called ListBox1 in the active worksheet, the following line of code fails when you run the macro that contains it:

   ActiveSheet.Shapes("ListBox1").ControlFormat.ListFillRange = _
       "$A$1:$A$10"

However, if ListBox1 is a Microsoft Excel list box, as opposed to an ActiveX list box, the code works correctly.

RESOLUTION

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.

To set the properties of an ActiveX control, refer to the control and its properties by using syntax similar to the following:

   ActiveSheet.ListBox1.ListFillRange = "$A$1:$A$10"

STATUS

This behavior is by design of Microsoft Excel 97.

MORE INFORMATION

The "ControlFormat Object" Help topic in the Microsoft Excel Visual Basic Help file contains incomplete information that may be misleading. Specifically, the Help topic fails to address that the ControlFormat object is available for use only with Microsoft Excel forms controls that are inserted from the Forms toolbar. In Microsoft Excel 97, you cannot use the ControlFormat object with ActiveX controls.

The example in the "ControlFormat Object" Help topic works only if the first shape in the worksheet is a Microsoft Excel list box, not an ActiveX list box.


Additional query words: XL97 ocx active x
Keywords : kbdocerr kbcode kbprg
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb
Solution Type : kbworkaround


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: January 9, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.