Enter leading hyphen or plus sign as text in Excel without using apostrophe
Posted on 2012-09-03
I have a spreadsheet that is created and populated from Word using VBA. There are five data values in the spreadsheet taken from the Word file that created the spreadsheet and these will often contain strings such as "+2d" or "-5wk" meaning plus two days or minus five weeks.
When the values are of this form, the VBA formats the cells as Text and the initial data are placed in them as expected.
However, I want the user to be able to edit the cell values in Excel. It's possible to make entries with no leading sign, such as "3wk" but if there is a leading plus or minus sign, as soon as I enter a letter, Excel pops up a menu of special formats to do with dates, data bases, degrees and other things.
Can I use VBA to fix it so that entries are made as strings without the user having to precede them with an apostrophe?