+ Reply to Thread
Results 1 to 2 of 2

Maintain Formatting when Pasting Data into a Cell with Data Validation

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Maintain Formatting when Pasting Data into a Cell with Data Validation

    I have a number of cells/columns formatted to two decimal places where I've set the Data Validation to 'Decimal' so that the user can only enter a number, either as e.g. 12, which will display as 12.00 or say 34.6512113 which will display as 34.65. The sheet is protected.

    The problem I have is finding a way to maintain the formatting should the user copy and paste data into the cells, e.g. if the user copies and pastes say 12, this appears as 12 in the cell rather than 12.00. Is there any way for pasted data to maintain the correct cell format, which in this case is decimal to two decimal places and likewise to force the Data Validation which I've set up should the user attempt to paste anything other than numbers. At the moment it is perfectly possible to paste letters, despite the data validation.

    I appreciate I could remove the option for the user to paste into the cells at all but that isn't a practical option in this instnace and could lead to numerous user errors should the user have to re key data.

    Many thanks

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Maintain Formatting when Pasting Data into a Cell with Data Validation

    One way is to simply reapply the numberformat that was on the cell when value changes. Of they "Paste" into a watched column, it fixes itself this way, while other columns are ignored.

    This example watches columns D (4) and G (7).

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Prevent copying and pasting a cell from a column with data validation to another column
    By kieranoduill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-11-2013, 10:02 AM
  2. Data Validation and Cell Formatting in Excel 2003 - Update Delay
    By dpcp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2012, 06:22 AM
  3. Data Validation and Pasting
    By Jim28 in forum Excel General
    Replies: 5
    Last Post: 06-23-2011, 11:31 PM
  4. Replies: 5
    Last Post: 02-11-2010, 05:16 PM
  5. Replies: 1
    Last Post: 07-31-2006, 04:13 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1