XL97: Run-Time Error 438 If Macro Uses ControlFormat Object (171075)
The information in this article applies to:
- Microsoft Excel 97 for Windows
This article was previously published under Q171075 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 professionals 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.
| Modification Type: | Major | Last Reviewed: | 11/2/2000 |
|---|
| Keywords: | kbcode kbdocerr kberrmsg kbprb kbProgramming KB171075 |
|---|
|