Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2006
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Formatting - Text (2003)

    If I have a cell formatted as text how can I stop other users from overwriting the format when they copy and paste from a cell which isn't in text format?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Formatting - Text (2003)

    Welcome to Woody's Lounge!

    They'd have to use Edit | Paste Special > Values.

  3. #3
    New Lounger
    Join Date
    Mar 2006
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Formatting - Text (2003)

    But it has to be idiot-proof - I am trying to generate a template to be used by a number of users and there is no way to guarantee that they'll all do the right thing. Any thoughts?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Formatting - Text (2003)

    You'd have to protect the worksheet, and create a macro that unprotects the worksheet, pastes as values, then reprotects the worksheet. You can assign the macro to a toolbar button and/or keyboard shortcut.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Formatting - Text (2003)

    Can you be a little more specific about what you are trying to accomplish? For example, why are you putting numeric values into text cells? You might be able to use the worksheet change event routine to convert the pasted values back into text. However, we would need to know exactly what these values look like and what would be the rules for converting them back to text (for example, are there leading zeros that would have to be put back).
    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Mar 2006
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Formatting - Text (2003)

    Yes thanks I probably should clarify a little further.

    I am trying to produce a template to collect data from a number of other users. The data includes dates, numbers etc. Some users provide numbers with leading zeros and some don't, some provide dates with 8 digits and some with 7 (e.g. 03/03/2006 Cf. 3/03/2006). I am also unsure of whether the data will be directly entered or whether it will be pasted from another source. I am looking for a way of providing these users with a template that can accept their data in any of these formats and which I can then process.

    I thought that by formatting the cells as text I could accept any format and then perform an operation on them to get them in the form I require - but this falls down if a user was to paste in numbers over the top of a cell formatted as text.

    Thanks for you help so far!

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Formatting - Text (2003)

    You still have not explained how you want to process the data and what form you want the data in. It would seem that if you want to "process" the data, then dates should be entered as dates and numbers as numbers. There are reasons for entering numbers as text (ie. telephone numbers, Social Security numbers, part numbers with leading zeros), but I dates are normally better off being entered as dates as long as they are within Excel's valid date range (after Dec. 31, 1899). In your example of 7 or 8 digit dates, both of those can be eaxily handled using Excel date values and then using the cell format to put them in a consistant display format. We really need to know what you are trying to do to help.
    Legare Coleman

Posting Permissions

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