Preventing Autoformat (Excel 2000)

Topic

New Reply

You can prefix the cell value with an apostrophe ‘ – this also makes the value into text.

Reply | Quote

Replies

That works, was hoping it was some obscure setting buried somewhere but your solution will get us by. Thanks!

Jim

Reply | Quote

That works, was hoping it was some obscure setting buried somewhere but your solution will get us by. Thanks!

Jim

Reply | Quote

When a user enters data such as a part number that contains the dash, for example 12-3366, in a cell Excel automatically changes this to a date.
I can get around it temporarily by formatting the whole column as text but anytime a search and replace is done it reverts any changed to a date again. Help!!

Reply | Quote

When a user enters data such as a part number that contains the dash, for example 12-3366, in a cell Excel automatically changes this to a date.
I can get around it temporarily by formatting the whole column as text but anytime a search and replace is done it reverts any changed to a date again. Help!!

Reply | Quote

I just found your solution to this “ever so slight” irritation. It works as advertised, but is there an alternative workaround, achievable using formatting applied to the spreadsheet at the outset? I don’t think your method would be appropriate for average “data entry” users.

Alan

Reply | Quote

As far as I know, the data entry “interpretation/translation” is one of those “helpful features” that work correctly 90% of the time to know what you want, but in some cases (like the example) if it “guesses wrong” you have to manually fix it…

Steve

Reply | Quote

The specific situation I’m trying to deal with involves the generation of a new sheet, based on a skeleton sheet. The latter contains data like Jan-xx, Feb-xx, … and the xx values are determined for the new sheet from the year that the user inputs. For instance, if the new sheet was for 1985, those skeleton entries would appear as Jan-85, Feb-85 etc. on the new sheet.

Using Hans’s apostrophe method gives the correct result, but without it, everything goes awry on both the skeleton (which is only copied from, not written to) as well as the newly created sheet.

Alan

Reply | Quote

When they are they are entered manually or via code it needs the prefix (‘) character.

How exactly are things being entered that it causes problems? There could be alternate entry means:
If they are specific cells, you could format as text before entry, then they will be text.

You could enter only a portion of the string (or in multiple places) and use a formula to combine them.

I don’t understand your “setup”

Steve

Reply | Quote

Each new sheet represents all of the accounting facets/ tables for a particular calendar year, specified as say, 2008. The whole thing is done in VBA, fired from the NewSheet event. The skeleton worksheet is copied to the new sheet, then a few search & replaces change “YYYY” to “2008”, “yy” to “08” and “xx” to 08-1=07. All this works OK now, using the apostrophe method in the skeleton sheet e.g. Jul-yy will correctly become Jul-08, 31/12/yy will become 31/12/08, “Report for YYYY” will become “Report for 2008”.

My concern arises from the more general situation of users inserting their own dates (intended as Text) in non-specific areas of the sheet – the skeleton copy is not the whole story. I suppose I could format the whole thing as Text, then reformat the known cells as currency, number etc. but I was hoping for an easier/ alternative way.

Alan

Reply | Quote

Your “problem” is the user and allowing them to insert dates that should be text – perhaps education/training will help…

Maybe only allowing entry into particular cells…

Search/replace can become an issue, since it will most times, treat it like a “re-edit/re-entry” and interpret/translate the new item…
Perhaps created named formulas for “yyyy”, “yy”, “xx” and use the named formulas in formulas. Then all you have to do is change what the names “refers to” to update them…

Steve

Reply | Quote

That sounds like a good idea. I’ve not used names for formulas before. Something like this I guess:

Names.Add Name:=”YYYY”, RefersTo:=”=UserInputValue”
Names.Add Name:=”ZZZZ”, RefersTo:=”=UserInputValue+1″
Names.Add Name:=”xx”, RefersTo:=”=Right(UserInputValue-1, 2)” etc.

where user has supplied (validated) UserInputValue through an InputBox.

As you say, it’s a user issue in the very end, and unfortunately it ain’t my workbook to rule. I think a lot of freeform text might end up going in, from a number of users who pass it round… all out of my hands. I’ll have a fiddle with your idea. Thanks Steve.

Alan

Reply | Quote

You can have the inputbox code do some validation, and then have it put the results in the name.

When you add the names, you might also set the visible property to false (optional parameter in Names.Add) so they do not show up in the insert- names dialog…

Steve

Reply | Quote

---

You can have the inputbox code do some validation, and then have it put the results in the name.

---

That’s what I was alluding to when I said “user has supplied (validated) UserInputValue through an InputBox”. This seems to work well (so far) for this project, where an added sheet might be for an annual report, or something totally unrelated. I validate according to the sheet name they enter e.g. “2008” would be a report based on the skeleton.

Good thought setting the visible property to false too.

cheers
Alan

Reply | Quote

A simple method is to format the cells as text, that way, any entry will stay unchanged. Be aware that you only do that to cells that need this behaviour, because Excel will treat formulas in those cells as text too.

Reply | Quote

Thanks Jan. I thought of this in post 462783: “I suppose I could format the whole thing as Text, then reformat the known cells as currency, number etc. but I was hoping for an easier/ alternative way.”

I guess an easier way might be to start with a blank sheet, formatted entirely as text, then “manually” copy/ paste all of my non-blank cells across. That doesn’t sound too bad. I think it’s the user’s problem to do the reformatting as required, and I imagine most of the user additions will be intended as text anyway. I doubt they’ll be adding much/ anything that leans towards calculation.

cheers
Alan

Reply | Quote