Add inputmessage without removing existing data validation

[MrsAlice]

Excel 2007
I have a worksheet that has several ranges with data validation (for example allowing only integers or list items). I also run the macro below to add the last modified date in the "input message". The problem is that the ".delete" part of the code removes the preexisting data validation.

I cannot find a way to circumvent this problem: I tried simply disabling the ".delete" part of the code, didn't work. I tried writing code that that would store the old data validation and combine it with the inputmessage, but I failed. Is this possible, and if yes - could you point me in the right direction?

Thanks!

Please Login or Register  to view this content.

P.S. should I include the ".delete" code I mention in the first two paragraphs in #code tags on this forum? It seems to me that doing that for a single word would make things a bit messy.

[MarvinP]

Hi MrsAlice and welcome to the forum,

I'd suggest you not do data validation on those cells and do the entire checking using VBA.
Use code in the Change Event and check whatever is input. VBA can do what Validation does with messages and more.

[mikerickson]

Try something like this:

Please Login or Register  to view this content.

The odd formulation of the intersection is because of a quirk of special cells.
If rng is a single cell, rgn.SpecialCells returns the same as applying .SpecialCells to the whole sheet.

[MrsAlice]

Thank you MarvinP for your recommendation and Mikerickson for your code. I will look into data validation through VBA at a later stage.

Mikerickson's code works perfectly on cells that contain data validation. A cell that does not contain data validation does however not receive the "timestamp". After reading my opening post again I realized I did not describe the problem specific enough. I apologize for that!

Is there a way to modify the existing code to apply to the specialcells & regular cells all the same?

(In this imperfect world I just gave data validation to all cells that did not so far, text length less than 99999)