+ Reply to Thread
Results 1 to 2 of 2

cell format locking and data validation on paste

  1. #1
    Registered User
    Join Date
    07-06-2009
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    1

    cell format locking and data validation on paste

    I have created an excel template file that users will be pasting data into, from other excel spreadsheets. The first column of data is numeric (integer). Somebody pasted data for which the first row contained a number that had an warning that said "The number in this cell is formatted as text ...", and that warning was carried over into the pasted worksheet. We are using this template file to upload data into an asp.Net application, and because of this warning, the data for that cell was blank.

    Is there any way to lock cell formatting so that when a user pastes data, it converts the data to numeric, or doesn't allow the data to paste, or something like that?

    I want to allow for data to be pasted, but I want to enforce some kind of data typing on the pasted data??

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: cell format locking and data validation on paste

    Is there any way to lock cell formatting so that when a user pastes data, it converts the data to numeric, or doesn't allow the data to paste, or something like that?

    I want to allow for data to be pasted, but I want to enforce some kind of data typing on the pasted data??
    I think your best options are
    1. Educate your users to use Paste Special Value
    2. Enforce correct data types in the source to prevent text value where a number is expected

    You can't lock a cell and simultaneously allow pasting of data.
    Data Validation is overwritten by paste actions
    VBA can be disabled so it is not secure either

    One more possibility: Two-step paste operation using a "dummy" sheet to received the initial paste then a macro to copy and paste special values to real sheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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