I will try to explain again. I hope you have observed there is a worksheet event that only allows certain characters to "stick" to cells in range "ORDER_CHARACTERS" (H4:H18). These characters are t, c, d, v, h and s. Any other character that you write in these cells are excluded and the valid letters left are sorted according to the order "tcdvhs". Each character represents a certain scope in a car insurance.

1. Let's say I want to make an entry in H10 where data validation i activated. Here I am of course restricted to the data validation reference. Let's say E10 = "item 2" which restricts data validation for E10 to range "RANGE_VALID_COMBINATIONS_FOR_FREE_TEXT_WITH_ITEM_2". Now I can either choose to write "v" or "vs". I write "vs" and data validation swallows this.

2. Let's now say I hand this to someone less familiar with the logics of the order in which the characters must follow each other. Instead of the valid order "vs" this person writes "sv" in cell H10 and of course data validation pops up saying this is a bad entry.

BUT: I would like for the data validation to hault for a moment until the worksheet change event has run and sorted the characters "sv" into "vs" and then for the data validation to check if it is a value among those in "RANGE_VALID_COMBINATIONS_FOR_FREE_TEXT_WITH_ITEM_2." In this way I would allow for an inexperienced user to either write "sv" or "vs" to represent the combinated scope "vs" as either really equals "vs".

Please compare this between H4 and H10.

These are the options:


A. To have the data validation to hault until the worksheet event has run and ordered the characters.

B. Skip the Excel data validation as in H4 and add validation by vba code. This should of course run after the worksheet change event has run.


How could it be done? Please tell me in case there still is some confusion!