XL2000: ActiveX Controls May Not Display Formatted Numeric Data

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.

Text 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.

Microsoft 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 Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

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:

1. Save and close all open workbooks, and then open a new workbook.
2. On Sheet1, type the following:

    
         A1: 1/1/97
   
         A2: 5/1/97
   
         A3: 10/10/97
   					

3. Start the Visual Basic Editor.
4. On the Insert menu, click UserForm.
5. Add a combo box control to the user form and set the RowSource property to Sheet1!A1:A3.
6. 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
   					

7. Run the user form and click the arrow on the combo box.
8. 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:

1. Save and close all open workbooks, and then open a new workbook.
2. On Sheet1, type the following:

         A1: 1/1/97
   
         A2: 5/1/97
   
         A3: 10/10/97

3. Start the Visual Basic Editor.
4. On the Insert menu, click UserForm.
5. Add a list box control to the user form and set the RowSource property to Sheet1!A1:A3.
6. 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
   					

7. Run the user form and click any date in the list box control.

The selected date is displayed in a message box.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

For 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.