When pasting data into a column with data validation I lose validation check

Hi All,
Im using MS excel 2000 sr1, I have set up some basic validation on a
spreadsheet column to say only allow whole numbers between 1 and 10
(as an example). However it is possible to type the number 11 in
another column (with no validation) in a cell and then cut and paste
this number into the column that has validation applied to it. The
data validation to check for numbers between 1 and 10 does not trap
this error or complain I.e. I believe it is taking the formatting from
the cell I typed 11 in (which has no data validation) and applying it
to the cell Im pasting to, which is in the column that has data
validation (I hope this makes some sense).
I understand you can do paste specials but unfortunately my users
often work on their own spreadsheets and then just paste (Ctrl-C,
Ctrl-V) in large chunks of data so I want an automated method to
enforce the validation if possible!? Drop down lists etc is not really
option, I believe it may need to be some code / macro or some global
option I can activate.

Any ideas greatly appreciated

many thanks
Brad
0
obhayes (2)
4/16/2004 3:39:05 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
754 Views

Similar Articles

[PageSpeed] 9

Hi Brad
not much you can do against this copy/paste problem. The only thing
would be creating an event macro which checks the entries manually. So
there's no such option to disable this copy/paste behaviour.



--
Regards
Frank Kabel
Frankfurt, Germany


Brad wrote:
> Hi All,
> Im using MS excel 2000 sr1, I have set up some basic validation on a
> spreadsheet column to say only allow whole numbers between 1 and 10
> (as an example). However it is possible to type the number 11 in
> another column (with no validation) in a cell and then cut and paste
> this number into the column that has validation applied to it. The
> data validation to check for numbers between 1 and 10 does not trap
> this error or complain I.e. I believe it is taking the formatting
from
> the cell I typed 11 in (which has no data validation) and applying it
> to the cell Im pasting to, which is in the column that has data
> validation (I hope this makes some sense).
> I understand you can do paste specials but unfortunately my users
> often work on their own spreadsheets and then just paste (Ctrl-C,
> Ctrl-V) in large chunks of data so I want an automated method to
> enforce the validation if possible!? Drop down lists etc is not
really
> option, I believe it may need to be some code / macro or some global
> option I can activate.
>
> Any ideas greatly appreciated
>
> many thanks
> Brad

0
frank.kabel (11126)
4/16/2004 3:43:14 PM
Just an idea, but what if you create a macro with a button on the spreadsheet that paste special/values and tell your people to use that button instead of Ctrl+V when pasting? Would that work?
0
anonymous (74722)
4/16/2004 4:46:02 PM
Hi,

Frank's correct.  Try something like this
(It's only looking at cells C1 thru C5):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("C1:C5")) Is Nothing 
Then Exit Sub
    With Target
    If InStr(1, Str(.Value), ".") Then MsgBox ("Nt an 
integer")
        Select Case .Value
            Case 1 To 10
            Case Is < 1
               MsgBox ("You entered " & .Value)
            Case 11
               Range("C2").Select
            Case Else
               MsgBox ("You entered an invalid number")
        End Select
    End With
 End Sub

Jeff


>-----Original Message-----
>Hi All,
>Im using MS excel 2000 sr1, I have set up some basic 
validation on a
>spreadsheet column to say only allow whole numbers 
between 1 and 10
>(as an example). However it is possible to type the 
number 11 in
>another column (with no validation) in a cell and then 
cut and paste
>this number into the column that has validation applied 
to it. The
>data validation to check for numbers between 1 and 10 
does not trap
>this error or complain I.e. I believe it is taking the 
formatting from
>the cell I typed 11 in (which has no data validation) 
and applying it
>to the cell Im pasting to, which is in the column that 
has data
>validation (I hope this makes some sense).
>I understand you can do paste specials but unfortunately 
my users
>often work on their own spreadsheets and then just paste 
(Ctrl-C,
>Ctrl-V) in large chunks of data so I want an automated 
method to
>enforce the validation if possible!? Drop down lists etc 
is not really
>option, I believe it may need to be some code / macro or 
some global
>option I can activate.
>
>Any ideas greatly appreciated
>
>many thanks
>Brad
>.
>
0
anonymous (74722)
4/16/2004 5:06:24 PM
Another option that's not as pretty.

Insert a helper column adjacent to the cells where you want the data entry.

Put the equivalent of your data|validation in that cell.  But make it return a
warning:

=if(dataInC3IsNotValid,"<--Error, Please Correct","")

(use a nice expression for that if statement.)

Then you could check to see if there are errors in any important calculation:

=if(countif(d1:d999,"*error*")>0,"Please correct your errors",yourformulahere)

If you have code that must run, you could also check for errors before it does
anything important.

Brad wrote:
> 
> Hi All,
> Im using MS excel 2000 sr1, I have set up some basic validation on a
> spreadsheet column to say only allow whole numbers between 1 and 10
> (as an example). However it is possible to type the number 11 in
> another column (with no validation) in a cell and then cut and paste
> this number into the column that has validation applied to it. The
> data validation to check for numbers between 1 and 10 does not trap
> this error or complain I.e. I believe it is taking the formatting from
> the cell I typed 11 in (which has no data validation) and applying it
> to the cell Im pasting to, which is in the column that has data
> validation (I hope this makes some sense).
> I understand you can do paste specials but unfortunately my users
> often work on their own spreadsheets and then just paste (Ctrl-C,
> Ctrl-V) in large chunks of data so I want an automated method to
> enforce the validation if possible!? Drop down lists etc is not really
> option, I believe it may need to be some code / macro or some global
> option I can activate.
> 
> Any ideas greatly appreciated
> 
> many thanks
> Brad

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
4/16/2004 11:17:32 PM
Thanks for the pointers / ideas, much appreciated.

Brad
0
obhayes (2)
4/17/2004 12:11:24 PM
Reply:

Similar Artilces:

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

incorrect check / cheque number printing
When generating a cheque run, let’s say cheque 61, 62, 63 and 64, the information printed on cheque # 62 is actually cheque # 64 and vice versa. This does not happen every cheque run, but it does happen fairly often. I have not seen GP do this. What version and service pack of GP are you using? Are you 100% sure the check stock itself is not out of sequence? Do you have any customizations? -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Syed" <Syed@discussions.microsoft.com> wrote in message news:3B02ECEF-7436-4CF3-930E-AA4D2DFDF117@microsoft.com... ...

VBA to check for latest version of front end?
I have a split database where the users have a local copy of the frontend. I would like to use VBA to check whether the users have the mostrecent version. I have set up the following tables:tVersion-Back-End (link to table in the BackEnd version)tVersion-FrontEnd (local table in the FrontEnd version)Each has a field Version where I am planning to put in the versionnumbers.Now, I am just starting to use VBA in my Access projects. Can someonepoint me to some coding to accomplish my task above? Can someone alsopoint me to a good resource (book, website, course) that would take mefrom a beginn...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

why does my spell check not work?
My spell check has never worked. What can I do to set it up? In what program / version "karen" <karen@discussions.microsoft.com> wrote in message news:DA8883BD-0958-4AB0-93FF-F43E4FB9D176@microsoft.com... > My spell check has never worked. What can I do to set it up? ...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

Sizing drop down validation box
I have a form with lots of drop down validation boxes, many of them are Yes,No,N/A or Pass,Fail,N/A so one a narrow box is required but all the drop downs are about three inches wide. Once I accidentally go them to the right size and saved the file, closed it, then re-opened and the drop downs were really wide again. Does anyone know have to set the width, I always thought they were the same width as the cell they pop from but not the case. Wayman Those extra wide dropdown lists appear occasionally, usually if cells on the worksheet have been merged (not necessarily the cell with t...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Start macro creating a mail with contact data and autotext
Hallo, I am working with an user form. The developing of that form started with Outlook XP with a lot of code inside for different buttons. I changed to Outlook 2007 and unfortunately the code of the form was not longer displayed. What I learned about this is that MS does not support to much code in the form (or maybe a bug). They also do not support any longer. I was sending this form to MS support but they told it is do much code inside and they do not know, why the code is not displayed. In Outlook 2003 the code is displayed as in Outlook XP. Because I do not know real...

find data and autopaste when found
Hi, Can someone help me how to do this : For checken the backorders of our customers we can extract a list fro our SAP system. this list is always different and shows us ever product per customer in Back order. ex. Customer A has product 1 en in backorder. This gives 2 lines in the xls file. can excel put th name of the customer on a form and it's backorders automatically. Ca it create for each customer showing in the list a new form? thanks koenraa -- Message posted from http://www.ExcelForum.com ...

Disappearing data in sync'd forms
I have a small sized text box in a form called frmMain. This text box may or may not contain a large amount of text. If the text box does contain alot of text I want to open up a new form called frmLargeText that contains a larger text box to allow the user to easily see and edit the large amount of text. I also need the two forms to stay in sync. i.e. if the user moves to a new record then both forms move to the same record. I thought I could do this by setting the recordset of frmLargeText to equal the recordset of frmMain as follows: Dim frm as Form_frmLargeText Dim rst As DAO.Rec...

Column searching problem
I have a worksheet called "net" containing the following: NETWORK AVG MIN MAX STD SAMPLES ABC 17.17 16.26 17.71 0.4469 19 CBS 12.99 11.69 14.56 0.6524 30 NBC 15.39 14.08 16.70 0.7323 38 NBC* 15.12 14.08 15.99 0.4910 31 Fox 10.63 9.07 12.09 0.9374 8 HBO 10.94 9.07 13.22 1.2507 19 Showtime 11.52 6.84 13.23 1.9017 11 HDNet 18.14 14.22 18.85 1.1619 14 HDNet* 18.45 18.13 18.85 0.2847 13 HDNet-Movies 1...

Column help
I have a seating chart I am trying to design for our graduation ceremony. Right now I have the report set up using columns. I have a rectange box with the graduates first, middle and last name in the box. I need 16 columns across the page for the seats which I have working. My problems are that I need a bold line going down the middle to divide the 8th and 9th rows. ( Students come in from 2 lines and meet in the middle of each row.) My next problem is that I need to have another column to the left of the 1st column and one to the right of the 16th column that will count the row num...