Results 1 to 14 of 14
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Prevening Autoformat (Excel 2000)

    You can prefix the cell value with an apostrophe ' - this also makes the value into text.

  2. #2
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevening Autoformat (Excel 2000)

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

    Jim

  3. #3
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Preventing Autoformat (Excel 2000)

    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!!

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevening Autoformat (Excel 2000)

    I just found your solution to this "ever so slight" <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> 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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevening Autoformat (Excel 2000)

    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

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevening Autoformat (Excel 2000)

    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevening Autoformat (Excel 2000)

    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

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevening Autoformat (Excel 2000)

    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevening Autoformat (Excel 2000)

    Your "problem" is the user and allowing them to insert dates that should be text - perhaps education/training will help... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    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

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevening Autoformat (Excel 2000)

    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

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevening Autoformat (Excel 2000)

    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

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevening Autoformat (Excel 2000)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevening Autoformat (Excel 2000)

    Thanks Jan. I thought of this in <post#=462783>post 462783</post#>: "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

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevening Autoformat (Excel 2000)

    <hr>You can have the inputbox code do some validation, and then have it put the results in the name.<hr>
    <img src=/S/yep.gif border=0 alt=yep width=15 height=15> 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •