Substitute Merged Cell With Data Validation With A Non-Merged Alternative

[Ark68]

I'm hoping someone is able to relate to an issue I am having, and be able to provide some sort of advice, direction etc. It's not a major project halting problem, nor is it creating an error, but it is an inconvenience.

On my worksheet, I have a merged cell (B6), merging range B6:F6. This "cell" has data verification, associated to it, a list. The dropdown arrow sits nicely to the right edge of the merged cell (at F6), and fits the contents nicely when a value is selected. The issue I am having is with the error handling of an inappropriate value entered into the cell that data validation captures. I am unable to create a custom error message through the data validation. When I try, I am given the message "The selection contains more than one type of validation. Erase current setting and continue?". Of course, I have to decline that.

No, yes ... merged cells bad! I know. I suspect that the data validation is being applied to each cell of the range of the merged area.

But I don't know a workaround, so here I am. I have been trying to work with CenterAcrossSelection, which really only formats the value across the range. I can only apply data validation to the one cell, leaving the other 4 cells vulnerable to access without data validation. The dropdown arrow is beside B6, so not representative of the broader range. If I move the data validation to F6, the dropdown arrow is outside the cell to the right, but now the values in the list are illegible because the column is too narrow. Also, that option requires the user to click on F6 to get the values. They can still inappropriately enter data on one of the other 4 cells that form that user entry field.

With the merged cell option, it is more user friendly, but it results in the default error box, with the retry, cancel and help buttons. Retry is functional, cancel takes me to a point in my code that causes an error (I would have to adapt code to handle it at that point but don't know how to identify that the cancel button of the default box was clicked), and help is useless other than bringing up generic Office Help.

I could add code to B6 change event to trap any unwanted value, but how do I prevent the default data validation error box from interrupting?

What do you pros suggest as a workaround or what option do you think is reasonable?

 

[jasonb75]

Maybe I'm missing some nuances but I can't recreate the problem.

I only get an error warning by typing something that is not in the dv list, are you saying that you want such entries to be accepted?

 

[Ark68]

Hi Jason, your reply is appreciated.
No, I only want the the values in the DV list to be accepted. When the user types anything else in the merged cell, I get the generic Excel error (Default message, no title, and three options). I would prefer a custom message, but I am unable to access that those setting because of the warning. When I apply the data validation list and custom error message to just the one cell in the range, B6, and not merged the custom error message plays. As soon as I merge the cells, I lose the custom message.

I think the workaround is to accept all values by suspending the error catching via code and just writing code in the cell change event that checks if the user entry exists in the list. Kind of defeats a valuable feature of data validation though.

 

[mikerickson]

Un merge the cells. Instead select B6:F6 and format the Cell with Horizonatal Alignment - Center Across Selection.

 

[jasonb75]

The way I did it was to merge, then apply the validation to the merged range, no warning messages for items in the list, custom warning for anything that wasn't.

 

[mikerickson]

Merged cells are to be avoided. You will get the same visual effect by using Center Across Selection.

 

[jasonb75]

Merged cells are to be avoided. You will get the same visual effect by using Center Across Selection.

Normally I would agree and would have said the same thing in my first reply, but it you read post 1 in full then you will see why I didn't.