Preventing Date Conversion from Text

[mirfield]

Hi,

I am having an issue with text being converted to a date when I need to keep the text.

Background : I have created a form with two ComboBoxes, one which contains the name of all open workbooks, that is loaded during initialization, and the other that is loaded with Worksheet names of any selected workbook - loaded when the first ComboBox chages.

When loading the woeksheet ComboBox, I first create an array with all the worksheet names and then sort them, moving them to a range on a spare worksheet and rng.sort, before moving back to the array and loading the ComboBox.

Issue : This works well most of the time, but if the workbook contains Sheet names such as "Oct 20011", "Dec 2011" then when I copy the array to the range (rng = Application.Transpose(WSarray) in the code below), the text is converted to a date value. So when my ComboBox is displyed it contains "01.10.2011" and "01.12.2011" instead of the correct names.

Is there any way to prevent the "Oct 2010", etc. being converted to a date?

For information, the code is below (the workbook name is in ComboBox "cbWkBk")

Please Login or Register  to view this content.

Edit : "Control" just disables/enables screen updating and autocalculation.

[mrice]

Try

Please Login or Register  to view this content.

in order to force a text value.

[mirfield]

Gah! The simple fixes are the best.

That works perfectly. Thank you.