checking pasted/entered values for validation match, but only 1x per worksheet

[DT211569x]

Over time I've developed this highly enforced workbook where end users can paste data and then SQL code is generated for me to input the data into our dB. The only worksheet in question here is the "data" worksheet, as well as some VBA code contained in the "ThisWorkbook" area.

Currently the workbook is doing everything I want it to - it is correctly enforcing that the data being pasted or entered matches the Data Validation settings (and not overwriting data validation settings by changing any Paste function to a data-only paste function), and prompting the user to correct the data if it doesn't meet the criteria.

Beyond just pasted data, I needed to implement code to also validate the data if the end user manually typed in a cell; I did that by setting up VBA to execute on any workbook change, which calls the postPasteValidation Sub and confirms that data entered matches.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The issue comes up when an end user edits a single cell - such as typing a note entry in Column N "W_Notes"; when they do this, it rechecks validation on EVERY cell in the worksheet (up to my defined range of 1001 rows). That takes approximately 45 seconds, which is not really an efficient use of time to write in a note in a single cell.

I've tried changing the setting in postPasteValidation from xlCellTypeALLValidation to xlCellTypeSameValidation, but that didn't seem to even decrease the amount of time for processing.

Please Login or Register  to view this content.

I'm OK with the "postPasteValidation" Sub running only 1 time before they close the workbook, and at that time checking all cells in the target range (and removing it from being called within the Paste and Workbook_Change Subs); but when I tried to move this code out to a Before_Close or Before_Save area, it wouldn't run.

I've attached a blank of the workbook here; the password to unlock the "data" tab is PW . I'm open to any and all suggestions, the end users love this workbook, but not the 45 second wait any time they need to edit a cell!

David

[MatrixMan]

Hi David. First, a few adjustments to your code ...

In the Worksheet_Change event, you unprotect the document to avoid the prompt:

Please Login or Register  to view this content.

Just turn off alerts like this:

Please Login or Register  to view this content.

And turn them back on at the end with EnableEvents and ScreenUpdating.

I don't understand the point of this bit of code, which undoes the user data entry and redoes it, including selecting the cell that is already selected, which means all this does is call postPasteValidation.

Please Login or Register  to view this content.

Looking at postPastValidation, it calls ConvertCase - twice, for some reason - and checks all cells with the same data validation rule as the one edited (not sure this would work if someone filled right a value across multiple different validation rules).

Why is this needed at all? The data validation in the sheet would pop a message saying "you can't do that" if you enter data that violated the rules set (1-30 characters in col P, for example) and this occurs BEFORE the change event would fire.

Finally, looking at ConvertCase, as is the pattern, this Sub converts ALL cells with text to upper case every time it's called (and it's called twice each time the event is triggered; four times if triggered by PasteVal). It also has code that doesn't seem to be useful; this bit conditionally sets a range variable to either the entire used range or the selection:

Please Login or Register  to view this content.

And then it is immediately set to "all text cells". The MsgBox will never pop up because you have text headers.

If I've understood what you're trying to do, then the following code should be all that's needed:

Please Login or Register  to view this content.

One final tip; in col P (for example) you have a validation rule based on text length. This will not fire if someone enters a number, even if that number has > 30 characters. Instead, use a custom rule to restrict the length of whatever is entered. See col P in the attached.

Hope that helps - if the above is not covering everything you need, let me know and I'll take another look. MM.

[DT211569x]

MatrixMan - THANK YOU for taking the time to read through and try to digest this amalgamation of code This code was built a little at a time, mostly by coming up with a requirement and finding similar code online and then hacking and chopping at it to integrate it with what I already had, and accomplish what I was trying to do ... which is why the code is such a mess.

Here were the requirements, which grew over the last year as the workbook was used:

1. Include Data Validation (DV) on all pertinent cells to match the data limits of the database
2. Allow the user to only paste values including using both mouse right click Paste as well as Ctrl+v so as to not overwrite the Data Validation or formatting settings in this workbook
3. Enforce the DV settings above, and prompt the user to enter a DV matching value if something incorrect is entered
4. a few other checks that are included in the ThisWorkbook section

I integrated some of the changes you mentioned; others I could not get to work or didn't understand. The attached workbook is updated and working for all my test cases, and I've provided a sample data file that you can copy and paste into the main workbook to see the results of the data validation checks. If you open both workbooks and then copy the data from Book1.xlsx into the main workbook, using either Ctrl+v or right-click|paste the following should happen:

• data validation should prompt you to correct 4 errors - marked in red in Book1.xlsx. The correct data is shown in green in Book1.xlsx
• all formatting and data validation settings from the copied data will be stripped from it, prior to the paste completing so the the target workbook remains exactly the same, other than having data added to it
• you can now type in a single column (such as notes) and type anything you want, it will not scan all 1000 rows of data validated cells, and change the text to UPPER cases

Notes regarding your suggestions:
Removed the Unprotect and reprotect from the Worksheet_Change event

I am going to test further with this code once the users confirm that the workbook is working as expected (because without changing it, everything seems to be working. It may be extraneous code,if so I will remove)

Please Login or Register  to view this content.

postPasteValidation did call ConvertCase twice - that was simply a typo on my part. As you've probably surmised by now, the data validation would prompt the user if they directly edited a cell - but not if they pasted data
which could potentially overwrite the DV settings and therefore never prompt them. That's why I had to build in all the paste/data validation controls.

I agree that the Msg box in ConvertCase is pointless.

Great point about the data validation settings based on text length! Most of these DV settings were text-length based, so I've updated them here.

I'm going to continue using your suggestions to clean up this code, but needed to get a working copy back to the end users so once I saw everything working had to hit pause on my efforts.

I'll wait for your response, or if no response is needed will mark the item solved by tomorrow. I truly appreciate your time reviewing and commenting on this, I've learned from it!

[MatrixMan]

Great to hear it's working well for you ... if you want to ask about any bits of code you don't understand, feel free and I'll explain it.

[DT211569x]

Thank you - I will get back with you as I have time to "play" with cleaning up the code based on your suggestions above. I'm sure I'll have more questions about it, and you'll have better ways of doing it