Force format of data input by user as text

0
votes

I have an Excel sheet where the user can enter data into several cells. I want this data to be treated as strings, regardless if the user enters something as "Hello" or "123" or "12.12.2012".

I could partially resolve this issue by formatting the input cells as "Text". But now if the user enters a number such as "123" or a string that looks like a date ("12.12.2012"), it appears with a small green rectangle and Excel says that the number is stored as text, but that's exactly what I want. Same thing with the text "1.2.90" in cell A5 which is seen by Excel as "date stored as text". The text "AA" in cell A4 is OK.

When I click on the exclamation mark icon, I can choose to ignore the "error", which removes the green triangle, but as soon as I change the content of the cell by putting another number, the green triangle appears again.

enter image description here

Green field are formatted as "Text"

Is there any way to force Excel to leave the data entered as string ?

1
excelvba
Yes. There is It is called "Switching the Error Check Off" Also voting this question to be moved to SU as it is Off topic here. – Siddharth Rout
office.microsoft.com/en-in/excel-help/… – Siddharth Rout
@SiddharthRout: thank you that works.... But unfortunately this is a global Excel setting. I need the same thing but local to an Excel file. With your solution my Excel File will do what I want on my computer, but if I give the Excel file to someone else the triangles will appear again unless this person switches of error checking manually. – Jabberwocky
You can tun them off via code. BUt then here is a catch. The code won't run if the macros are disabled in other pc :) – Siddharth Rout
Yes it was tagged VBA which normally happens on formula questions as well. Since it was not specifically specified that you were looking for a VBA solution, it appeared to me that you were unaware of the Excel's inbuilt functionality. Usually people opt in for NON VBA Method over VBA Method. And hence I voted it to be moved to superuser.com and at the same time let you know about it so that if I was wrong then I can retract my vote which I did :) – Siddharth Rout

1 Answers

1
votes

NON VBA Version

You can switch off the error tracking from the File Tab. To read about it, check this MS Link.

VBA Version

To do that using VBA, you need to switch off the BackgroundChecking using Application.ErrorCheckingOptions.BackgroundChecking

Example

Application.ErrorCheckingOptions.BackgroundChecking = False

You can read more about it HERE