Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Virginia Beach, Virginia, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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???

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •