Data Validation circumvented by data forms (Excel 97/2000)

Topic

New Reply

Hi all,

A colleague of me made a spreadsheet for other users using Data Validation but was surprised to see that if the user wanted to add data using Data Form, the data he entered were not validated. They could enter anything they want. It is clear that he doesn’t want the user to enter data that are not validated. Anybody who knows how to circumvent this without VBA? I suggested him to disable the Data Form option, using VBA, but he doesn’t want this.

Reply | Quote

Replies

Well, he could cheat by inserting a row just beneath the label row, make the height very small, then the Data Form command will not work since it is no longer a true database. Unfortunately, if your friend is using this spreadsheet as a “true” database, this may cause other problems. If it is just for data entry, this might help.

Reply | Quote

Actually, he wants to facilitate data entry by using the Data Form tool, but he also wants his data validation to work. I tried it out and in some cases – I could not repeat it in a consistent way – I got the typical Data Validation warnings, even when I was making use of Data Form to enter the data. However, I could not find out why it was working one time and not consistently. Is there a MS article about that somewhere???

Reply | Quote

Hans,

I have not experimented with this, but think that the validation only occurs when the selected cell is the cell to be filled by the data form. Maybe some sheet change event can help in setting the correct cell as the selected cell. Just a thought.

Andrew

Reply | Quote

Andrew,

Thanks for your reply. I searched through the Knowledge Base articles and found this one confirming the problem.
I tried to use the sheetchange_event in combination with the Data Form, but this does not work in the straightforward way. It seems that by adding data to the sheet via Data Form, the sheetchange event is not triggered.

Reply | Quote

One solution is to set both the data validation and the form to get data from the same named range.
For the validation choose list and =RangeName. Works even if the range is on a different sheet.

Reply | Quote