XL2000: ActiveX Controls May Not Display Formatted Numeric Data (213698)
The information in this article applies to:
This article was previously published under Q213698 SYMPTOMS
Text box, combo box, and list box controls do not display data in numeric
formats, for example:
- If you have the date 1/1/97 in cell A1, and you set the LinkedCell
property of a text box control to cell A1, the value 35431 appears in the
text box. (This value is the serial value for the date 1/1/97.)
- If you click an item in a combo box control that is a date, the combo box
control displays the serial number for that date.
- If you attach a click event macro to a list box control, and you click a
date in the list box control, the Value property of the list box control
is the serial value for that date.
CAUSEText Box Control
If you create a text box control on a worksheet and you set the LinkedCell
property to a cell with a number format, the number format is not used in
the text box control.
Combo Box Control
If you create a combo box control on a worksheet or a user form, and you set
the ListFillRange property (for a worksheet) or the RowSource property (for
a user form) to a range of cells, the drop-down list uses the number format
in that range, but the selected value does not.
List Box Control
If you create a list box control on a user form, and you set the RowSource
property of the list box control to a range of cells, the items in the
list box control use the number formats in that range, but the Value
property of the list box control does not.
WORKAROUNDMicrosoft provides programming examples 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 article assumes
that you are familiar with the programming language being demonstrated and the
tools used to create and debug procedures. Microsoft support professionals can
help explain the functionality of a particular procedure, but they will not
modify these examples to provide added functionality or construct procedures to
meet your specific needs. If you have limited programming experience, you may
want to contact a Microsoft Certified Partner or the Microsoft fee-based
consulting line at (800) 936-5200. For more information about Microsoft Certified
Partners, please visit the following Microsoft Web site:
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
Text Box Control
To link a text box control to a worksheet cell, use a text box control from
the Drawing toolbar instead of the Control Toolbox toolbar.
Combo Box Control
To use a combo box control containing a range of cells on a worksheet, use a
combo box control from the Forms toolbar instead of the Control Toolbox
toolbar.
If you are using a combo box control on a user form, use a macro for the
Change event to set the Value property of the combo box control. The
following example uses a combo box control and a macro for the Change event:
- Save and close all open workbooks, and then open a new workbook.
- On Sheet1, type the following:
A1: 1/1/97
A2: 5/1/97
A3: 10/10/97
- Start the Visual Basic Editor.
- On the Insert menu, click UserForm.
- Add a combo box control to the user form and set the RowSource property
to Sheet1!A1:A3.
- Double-click the combo box control to display the Visual Basic module
behind the user form, and then enter the following code for the Change
event for the combo box:
Private Sub ComboBox1_Change()
ComboBox1.Value = Format(ComboBox1.Value, "m/d/yy")
End Sub
- Run the user form and click the arrow on the combo box.
- Select any date in the list.
The selected date is displayed as the current value of the combo box
control.
List Box Control
If you have a list box control on either a user form or a worksheet, use a
macro similar to the one for the combo box control to display the selected
value for the list box control. For example, follow these steps:
- Save and close all open workbooks, and then open a new workbook.
- On Sheet1, type the following:
A1: 1/1/97
A2: 5/1/97
A3: 10/10/97 - Start the Visual Basic Editor.
- On the Insert menu, click UserForm.
- Add a list box control to the user form and set the RowSource property
to Sheet1!A1:A3.
- Double-click the list box control to display the module behind the
user form, and enter the following code for the Click event for the
list box control:
Private Sub ListBox1_Click()
x = Format(ListBox1.Value, "m/d/yy")
MsgBox x
End Sub
- Run the user form and click any date in the list box control.
The selected date is displayed in a message box.
STATUSMicrosoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. REFERENCESFor more information about text box, combo box, and list box controls, click Microsoft Excel Help on the
Help menu, type controls in the Office Assistant or
the Answer Wizard, and then click Search to view the topics
returned.
Modification Type: | Minor | Last Reviewed: | 10/11/2006 |
---|
Keywords: | kbbug kbdtacode kbpending kbProgramming KB213698 |
---|
|