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,

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"

Alistair
Demo.xls

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.  


test.xls

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

test.xls

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.

Thanks
errors.doc

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 PC...in 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
Alistair

Hi,

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.

Thanks
Victor
Book1.xls

Hi,

Please help me.....