disable auto format in excel for office web components

I have a program I have written in VBA on top of excel that uses forms, and I have embedded an excel spreadsheet from the office web components toolkit in the form.  I am having a problem where something like '1-16' being entered triggers the auto-formatting and gets converted to '16 Jan'.  I tried to change the formatting of the cells, but instead of giving you a  'format' option, it only gives you a 'number format' option; there is no way to format the cells as text.  I was wondering if there is some property or a way of disabling this annoying formatting in code?  I know that you can precede things by an apostrophe to prevent this, but it's really not practical in my situation.
Thanks
peterseychellesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Maheshwar RSoftware DeveloperCommented:
Hi peterseychelles, try this:

1. Click Tools -> Options
2. On the Edit tab, un-check the box next to 'Extend Data Range Formats and Formulas'
3. Click OK
0
peterseychellesAuthor Commented:
Hi,
The excel web component doesn't have a menu bar, and the version of office I'm using is 2007 so I have the ribbon, however I don't want to disable this function in my actual excel, just for the web component plugin within my form.
0
RichardSchollarCommented:
I'm not sure you will be able to do this (although I'd love to find I am incorrect).  The only way I found of achieving this was to ensure these values are entered with a single quote preceding them eg:

'5-9

'8/9

Richard
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Maheshwar RSoftware DeveloperCommented:
I had the same problem.  

There is no way to "disable" the formating..

The way I got around this:  When you open a new spread sheet, all the cells are general.  What I did (before paste) is to select the entire sheet, and change the cell type form general to text.  Then the paste will work properly.

You can use this method on just a column if you wish.

Hope this helps.
0
RichardSchollarCommented:
Omaheshwar, you did read that the OP is using an Office Web Component Spreadsheet Control, and not a standard Excel worksheet?
0
Rory ArchibaldCommented:
(Not for points)
Richard is correct - there is no way of turning it off and no Text format option. You will need to use the apostrophe sadly. (I assume you already know that the OWC is not part of Office from 2007 onward)
Regards,
Rory
0
peterseychellesAuthor Commented:
Yes I'm aware that OWC is effectively deprecated and not really meant to be used any more...but it's so convenient!  Last I heard they were working on a replacement architecture to cover the functionality but that it wouldn't be around until office 2010 is out, which is  shame.  It woud also involve a significat re-write of my program which would take a while adn it's in constant use.

Thanks for letting me know anyway, at least I won't waste any more time chasing it!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.