Data validation macro bug

[EdwardStephenson]

Hello all

I have data validation of various kinds implimented within a worksheet. When you copy and paste a cell over a cell with data validation in, it overwrites the validation.

I've found the following macro which is meant to check that the named range of cells, "ValidationRange" still have data validation following any cell or range being edited in the worksheet. In this case I have named cells O9:R10,U9:AR10 as the range "ValidationRange" as these are the cells I have with preexisiting data validation. Found at this site: http://www.j-walk.com/ss/excel/tips/tip98.htm

Please Login or Register  to view this content.

This macro doesn't seem to work. Excel either responds with "Rune-time error '29': Out of stack space" with the debug option greyed out.

Or the message box pops up "Your last operation was canceled. It would have deleted data validation rules." And then either

1. I click OK and the box doesn't go away (basically reappears as soon as I click OK) or
2. I click OK and then get "Run-time error '1004': Method 'Undo' of object'_Application' failed" with the debug option greyed out

Whatever happens usually results in Excel crashing also..

Thanks all

[romperstomper]

You should always disable events if you are going to make changes in a Change event:

Please Login or Register  to view this content.

[EdwardStephenson]

Hi Rory

Thanks for the response. That code still allows me to copy and paste over validation which is what I'm trying to prevent!

[romperstomper]

I think the original code is actually flawed, although it shouldn't let you copy over the validation. Try something like this:

Please Login or Register  to view this content.

[EdwardStephenson]

Still allows copy and pasting to overwrite..

Thanks

[romperstomper]

Not for me. Can you post your revised version for testing please?

[EdwardStephenson]

Thanks

[romperstomper]

I see the message if I try and paste anything over the top of those cells and it gets undone. Try restarting Excel in case your events have become disabled.

[EdwardStephenson]

Ah - I restarted Windows and it works - thanks. Is there a fix that''ll make it always work? It will be a shared spreadsheet when its finished and I need to make sure its always functioning for all users.

[romperstomper]

I think it was caused by the problems you had originally - it should work in general use.

[EdwardStephenson]

Sharing the workbook prevents it from working - any ideas?

Thanks

[romperstomper]

You shouldn't be able to change Data Validation settings in a Shared Workbook anyway, so there shouldn't be any need for the code.

[EdwardStephenson]

Yes I know, but copying and pasting a cell with no validation over a cell with validation overwrites it, whether the workbook is shared or not

[romperstomper]

Not for me - I just tested it in your workbook. I'd advise against using shared workbooks anyway.

[EdwardStephenson]

Hmm, I suppose it's one of those things then. I'll contact our useless IT department.

Thanks for all your help