Prevent paste over cell data validation

[Dairy_Queen]

I searched and found this but neither it nor many other web-searched solutions seems to work. Can't figure what I'm doing wrong. I'm using Excel 2007 in Windows 7.

http://www.excelforum.com/excel-gene...on-cell-2.html

I have a number of users with their own spreadsheets, and I'm supposed to consolidate them so summaries and graphs can be made for reporting to higher management. The existing cells are full of mixed numbers, dates and text, so I'm creating a template with columns with data validation.

Of course, when they start using my template, they will want to copy/paste their original mixtures, and the validation will be lost.

Thank you for any help.

[HaHoBe]

Hi, Dairy_Queen,

did you put the codes behind the sheet where the event is supposed to work? And with the altered security management from 2007 on you have to make sure that the macros on the template are active to get the codes to work (trusted location as an example).

And please do not start multiple threads on the same topic (please have a look at the Forums rules for that - thanks in advance, other thread).

Ciao,
Holger

[Dairy_Queen]

Thank you for the prompt reply. I started a new thread because the Forum Administrator told me to; I had put it in the old one originally. Newbie, you know.

Now, I'll look at the trusted location and see if that's what's wrong.

I do have macros enabled, and the suffix on the file is ...xlsm. And I pasted the vba code in starting inside the sheet with the cell validation, using Alt-F11, and inserting the module into This Workbook. Then I exited with Alt-Q, saved the workbook, closed it, re-opened it, enabled macros, and tried to paste into a validated cell. It always pastes over and the validation is lost.

Have tried several different vba offerings from a variety of threads and web searches. No joy.

Thank you for offering to help. Do hope this can be fixed.

Judy
(aka Dairy_Queen)

[HaHoBe]

Hi, Judy,

the code doesn´t go into ThisWorkbook but must be behind the sheet. Right-Click on the worksheet Tab and choose View Code to get there for sure.

If you are located in the Code window there are 2 comboboxes placed over it. In the left one you can choose from all objects available in that section, in the right one all objects related to the selection are listed. So while you are in ThisWorkbook and choose Workbook you will find all events firing from there. In ThisWorkbook there is only Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) which would fire the event on every worksheet in the workbook. VBA will not recognize the Worksheet_Change-event in ThisWorkbook, only behind the sheet it is recognized and will get fired.

Ciao,
Holger

[Dairy_Queen]

It worked! Thank you, thank you.

I thought I had started out doing it that way and it hadn't worked then, but no matter. It works now. I can get on with the rest of the project at last.

Now to figure out how to give you credit for solving this...