VBA is Stubbornly Interpreting 10/12 as Date

[pkirklewski]

VBA is Stubbornly Interpreting 10/12 as Date

https://social.msdn.microsoft.com/Forums/en-US/f3c6afe8-36f1-43e7-b29e-68fd1a0432e4/vba-is-stubbornly-interpreting-1012-as-date?forum=isvvba

1. I’ve got a collection and a string variable declared

1. I select the sizes in my listbox and then populating the myCollection collection from that list

1. I am making sure that the currentValue is a string before I paste it in a cell

1. Still Excel is interpreting it as date !

1. THESE ARE SIZES NOT DATES! I don’t want Excell to format those cells for me and I think I’m making myself clear when I specifically say that this is a STRING and not a date or variant!
   
2. Also - each time (despite me formatting the cells manually) Excel annoyingly changes the cell alignment to the right so I need to select the range and change it to xlLeft

1. How do I stop excel formatting the cells for me when I don’t want it to ???

Please help

[Paul_Hossler]

Excel worksheet tries to be (too) helpful sometimes

Try applying number format first ...

Sheets(1).Cells(j+23,10).Numberformat="@"
Sheets(1).Cells(j+23,10).Value=currentValue

. .. or putting a single quote in front

Sheets(1).Cells(j+23,10).Value="'" & currentValue

[Aflatoon]

Format the cell as text first using .Numberformat = "@", or prefix the value with an apostrophe.