0

I have a user input box where you type in a string, annoyingly this string looks like a date 00/00/0000 and excel reformats it as such. When the value can't be a date ex. 18/19/4561 (month can't be 18 or 19) it displays it correctly. But whenever it can be seen as a possible date it switches things around.

I've tried setting the value as a string rather than nothing but excel still changes it when putting it in the page.

When I try manually inputting it in the cell or equal the values from a manually entered cell it works fine.

But whenever I get it from the inputbox it messes with it. Even when I hard code the string to a variable (x = "05/06/4564") it switches things around.

How do I force excel to leave the string as is?

Improve this question
3
  • Please consider marking the answer as "Accepted" if it has solved your problem. Thanks Commented Jun 19, 2015 at 13:29
  • Have you declared x as a string? And have you tried the Cstr() function? Commented Jun 19, 2015 at 13:54
  • @TMH8885: Yes I did try try both, and neither work. (mentioned the first one in my question btw) I believe the problem here wasn't VBA's way of handling the string. But Excel trying to be smart. Commented Jun 19, 2015 at 14:31

1 Answer 1

Reset to default
2

Prefix the value with a single apostrophe and Excel will interpret it as a string.

Eg '18/19/4561

Also, have you tried setting the cell format to Text

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Damnit so dumb of me, I've added a .NumberFormat = "@" before it runs and works fine now. Thanks. the apostrophe thing wouldn't really work for me as I would have to constantly filter that out and in again when doing something with it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.