Another source for breaking data validation is if the user select multiple cells and uses ctrl+enter to update all the selected cells. The validation of the active cell is evaluated, but not the validation rules in the other cells in the selection.
Thank you for the video. Suppose one of the future Excel upgrades will contain something like a new option in the "Protect Worksheet" list: "Prevent paste/drop to validated cells" or so
Copy paste has always been an issue with validation: constant irritation in my work! Thank you for this, but not sure we can use it because of restrictions on use of VBA
Office Scripts & Power Automate were designed to be the alternative. I've got a course about it, if you're interested: exceloffthegrid.com/office-scripts-course
I have certainly been caught by the cut-and-paste issue multiple times. Since my organization frowns on VBA, I will likely use conditional formatting. Thanks!
@@ExcelOffTheGridin my experience, there is never a unique way of solving issues, it always depends on the situation. Your channel has helped me a lot to find solutions for problems, so thanks a lot
Awesome stuff! One off-topic thing confused me though. How is it that our Worksheet subprocedures are set to “Private” and we still get to reference them inside a Workbook code section?
Another source for breaking data validation is if the user select multiple cells and uses ctrl+enter to update all the selected cells. The validation of the active cell is evaluated, but not the validation rules in the other cells in the selection.
Yes, that’s another good one.
@@ExcelOffTheGrid Or bad one. A VBA prevention method would be to put Target.Cells(1).Select in Worksheet_SelectionChange.
Thank you for the video. Suppose one of the future Excel upgrades will contain something like a new option in the "Protect Worksheet" list: "Prevent paste/drop to validated cells" or so
Would be nice. 👍
I noticed this situation yesterday. Thank you very much for the tips!
You're welcome!
Being aware of the issues around data validation is a major step in ensuring our data is valid
I agree 100%. Knowing the limitations allows us to create suitable processes to handle it. 👍
Copy paste has always been an issue with validation: constant irritation in my work! Thank you for this, but not sure we can use it because of restrictions on use of VBA
Even if you allow VBA in your organization, if a user does not click Enable Macros, then a user can still do anything.
A solid review process is key.
It seems macros do not work in Sharepoint. Any alternatives?
Office Scripts & Power Automate were designed to be the alternative.
I've got a course about it, if you're interested: exceloffthegrid.com/office-scripts-course
I have certainly been caught by the cut-and-paste issue multiple times. Since my organization frowns on VBA, I will likely use conditional formatting. Thanks!
Even I would be uncomfortable with the VBA option in here. I think conditional formatting and a solid review process is the best option.
I have applied conditiinal formatting and created a simple macro that loops the cells to check the background color and clear that cell with a msgbox
That's a very good technique. I'm guessing it took a while to find out how to do that.
@@ExcelOffTheGridin my experience, there is never a unique way of solving issues, it always depends on the situation. Your channel has helped me a lot to find solutions for problems, so thanks a lot
Awesome stuff! One off-topic thing confused me though. How is it that our Worksheet subprocedures are set to “Private” and we still get to reference them inside a Workbook code section?
Because I used the "Run" command and referenced the module.
Run enables us to exclude any Private macro as long as we know the name of it.
@@ExcelOffTheGridAwesome Mark! Thank you so much!
Would CTRL+R cause issues, or would the prevent copypaste catch it?
I think CTRL + R would break it - as you're not selecting another cell.
As far as I know, VBA does not work in SharePoint environment? Is this true?
Correct - it will only work in the desktop version.
Have you found a solution?
I've only just discovered that the moving dashed line around a cell is called 'marching ants'. 😮 🐜🐜🐜
I always assumed there was a proper name, but apparently it’s a common term in software design… en.m.wikipedia.org/wiki/Marching_ants
Data validation is not about paste actions. Cause copied cell also copy all properties with itself. :)
Excel 365 it doesn’t copy ALL the properties, it changes about 12 months ago.
On a protected sheet the DV list is no longer over written by a paste.
❤
😁