Link to home
Create AccountLog in
Avatar of victory_in

asked on

After entering data validation criteria in Excel 2003, autofill and copy/paste will override the validation criteria. Is there any way to prevent this?

Hi Gurus,

In excel I've drop down list box. If I copy and paste the values it will override the validation condition. Anyway to prvent this would be great help.

Avatar of agillanders
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of victory_in



What is this MyCopy() & PasteValueValidation() is the PUSH_BUTTONS? Please explain how to create this?
I know it it macro. I created the in the view code and do we need to create the push_button? or how we run the macors?

copy and paste is working fine. Everytime do I need to run the macro?

How to create customized button on the toolbar for easy access

Thanks much
The attached demo has the macros associated with a button on the sheet.

If you wanted this always available in Excel you would need to setup a custom toolbar or add custom buttons to oneof the existing ones.
In Excel 2003 right click on the toolbar space and select customize, or use the down arrow at the right hand side of each toolbar to "add a button"

Firstly thanks.
I've attached my sheet where in sheet2 has the list values and test has the validation(DC column). I need to validate the cut and paste functionality in DC column.  Attached is the excel sheet.  

OK. It works fine. Attached is your test with the code and buttons copied across.

Hi agillanders:
This file when i hit the MyCopy button it gave the following errors and also file should be at last converted as .CSV file. So button should not be there on the file.

Please help one more time.

That is a file access error and I can only see one way of making it happen. I suspect you have copied the buttons from my demo sheet into another workbook but NOT copied the VBA code module that goes with them. If that happens the buttons will look to open the original file that has the code module to get to the macros...that will be the path to the demo workbook on my this case 'C:\Users\agillanders\Desktop\Demo.xls'. Since that file obviously does not exist on your PC you get the error message you are getting saying it can't find it.
If you have the original demo.xls open as well as your new file the copied buttons should find the code they need from there. And in the absence of buttons on the worksheet or toolbar you can always just call the functions from the macro dialog directly.
  • Open the demo.xls workbook and minimize it...then open the workbook you are really interested in
  • Select the source cells you want to copy
  • Press Alt-F8 and select the MyCopy macro and press the Run button
  • Now select the top left destination cell
  • Press Alt-F8 again and select the PasteValueValidation macro and press the Run button
  • Voila!
This works even if the macros are in a different workbook as long as that workbook is open. When you use it like that the name of the macro in the dialog box is prepended with the name of the workbook containing the macros. But they work fine even if both the source and destination cells are in different workbooks from each other AND the macros. If a Workbook containing the macro code is not open you simply will not see the functions in the list presented in the macro dalog.
If you want the buttons, or a toolbar, in a specific workbook you have to copy the code as well as the buttons. All the buttons do is launch an associated VBA macro...if that isn't there they cannot do anything. Alternatively you add a toolbar and te code to the default template (or whatever template is driving your workbook) so that every workbook based on that template will get the capability. That is a whole other question and should be asked seperately on here if you want to get into that.
This solution does everything you asked for and a bit more (I'm using myself and I added a couple of variants).
Good luck

Thanks for help!!!!

But still my original question still remains as is.  All my question was when I use drop down list box whatver the values  i use from drop down list box, then it works fine and also if I type other values not there in the drop down list box still it will alert saying """Please enter the values from drop down list box"""". All is fine.

But if I paste a values which is not there in the drop down list box, it overrride the alert function. In this case it won't alert saying """"" Please enter the values from the drop down list box """"   Why?

For example in the attached sheet if you tye in 100 in the Disrtict Code column, it will alert ""Please enter the values from drop down list!!!""""" If I copy and paste 100, then it will override the it, it won't show me the alert """ Please enter the values from drop down list!!!!""""   Why?

I'm waiting for your reply.


Please help me.....