+ Reply to Thread
Results 1 to 14 of 14

Restrict data to certain values but does not work when paste row of values

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Restrict data to certain values but does not work when paste row of values

    hi,

    I have this sheet where on certain columns i use the "Data validation" to restrict the values of such cells to specific values.

    The problem is that i expect people to transfer data to the sheet using cut and paste from some other sheet............so if i cut-n-paste data from one sheet to my sheet which has the data-validation the paste seems to overide teh data validation...........any thoughts?

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Restrict data to certain values but does not work when paste row of values

    Two Ideas -

    Do data validation on the Cut from data.

    Write VBA Event macro that checks all new data in the pasted sheet to see if it's valid.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Restrict data to certain values but does not work when paste row of values

    how would i do either of those?

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Restrict data to certain values but does not work when paste row of values

    Give me a sample workbook and I can work on it.

    Do you always copy from a sheet that you have control over? Putting Data Validation on that sheet seems simple to me. I must not be saying it correctly...

    VBA to check the data is just knowing how to code.

    How about a conditional format that background colors the cell if it isn't in the list of valid entries? That would substitute for your Data Validation.

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Restrict data to certain values but does not work when paste row of values

    hi

    i did find this link / code not sure its what you were talking about............note......could not get the code in the linke to work properly though................

    http://www.j-walk.com/ss/excel/tips/tip98.htm

  6. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Restrict data to certain values but does not work when paste row of values

    is there a way to make vba check contents of certain cells if they are changed?

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Restrict data to certain values but does not work when paste row of values

    The topic is in Event Macros behind a worksheet.
    See http://dmcritchie.mvps.org/excel/event.htm

    It takes some VBA to do it but isn't much.

    Still need a sample.

  8. #8
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Restrict data to certain values but does not work when paste row of values

    thanks for the link. very interesting stuff.

    The link i provided if it worked seemed like something that might provide a soln for me. Buti cant get it to work.

    also, i am providing a file. In this file in sheet1 there are multiple cols but only those cols labeled "Col0, Col1, Col2...........etc" have data validation attached to them.

    the code from the link is in the "this workbook" section...........but it does not work......i keep getting an error or something about a stack getting full..........

    anyway, any ideas would be appreciated either using this method or someo ther
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Restrict data to certain values but does not work when paste row of values

    actually the code is placed into the Sheet1(Mastersheet) there is no code in the "this workbook"

    also there is code in the "Module" section.......again an attempt at something that di dnot work.

  10. #10
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Restrict data to certain values but does not work when paste row of values

    you still there?

  11. #11
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Restrict data to certain values but does not work when paste row of values

    if you need additional data let me know.

  12. #12
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Restrict data to certain values but does not work when paste row of values

    when i run it i get an output stack error..................any ideas?

  13. #13
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Restrict data to certain values but does not work when paste row of values

    Hi welchs101,

    It just takes me time to figure stuff out. It is all theory until I can give you an example. That is my "One Test is worth a Thousand Opinions" signature.

    Find the attached where I have created two named ranges. The first on the Master sheet is the range of cells under the words Col 0, Col 1 etc. On Sheet2 I have a second range with a list of things that are valid to put in the Columns on the Master.

    The code is behind the worksheet. It is event code that will only fire if something changes that is in one of the Columns. When it firest it looks at each cell that has changed in the Columns and compares it against the SearchList values.

    I think this is what you are wanting. It uses VBA and you can modify it to meet your expandng needs.

    I hope this explains what I was thinking.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Restrict data to certain values but does not work when paste row of values

    thanks. I will take a look at the file........thanks again.

+ 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