Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Location
    UK
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unique values but no warnings with Data validation (Excel 2000)

    Data validation is a nice way to give users a drop down for entering categories and I've written a couple of simple VB functions so that they get unique values based on the data they are entering.

    Only problem is that the warning message when entering a new category does not come up.

    (I know alt-downarrow gives much the same functionality but I wanted the drop down button and the warning... shame I can't find a way to access the alt-downarrow list directly though)

    Anyone know how to get the warning to come up?

    Cheers,
    Paul
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Unique values but no warnings with Data validation (Excel 2000)

    Paul

    I can't understand what you are trying to get to, Sorry <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    But if you have already wrote the code how about writing error checking in the code?! <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

    When <font color=red> ValueFound = False </font color=red> then you will tell your user something like:

    "You are entering a new category. Are you certain that there is not already an appropriate existing category?"

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique values but no warnings with Data validation (Excel 2000)

    I'm not positive of what you are trying to do. The message in the Validation is only going to be displayed when an invalid entry is made. Since it looks like your code immediately adds whatever is entered into the list, nothing is invalid and the message will never be displayed. It looks like you are going to have to display the message from your code.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Sep 2001
    Location
    UK
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique values but no warnings with Data validation (Excel 2000)

    True, the code maintains the list but I would have thought that the validation test would be done before the value is added so the code would not get to see the new value until after the warning had been issued and the user had confirmed their desire to add a new value.

    The idea of this validation stuff is to provide a 'soft' validation where the user gets drop down entry of existing items and is warned if they are entering a new value that has not been entered before.

    I can add the warning to the code but I'm trying to minimise the amount of code to keep things as simple and flexible as possible (it pained me to have to write the code at all but I can't see any other way to get the result I want (a pity because Excel has what I need in there some where to drive the auto-completion function)).

    Cheers,
    Paul

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique values but no warnings with Data validation (Excel 2000)

    I have never seen a list of the order in which events occur, so I guess that there is not a definitive way to tell. However, it appears that the cell change event is triggered before the validation event. This is not all together illogical since the change event could change something that would need to be validated.
    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Sep 2001
    Location
    UK
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique values but no warnings with Data validation (Excel 2000)

    Ah... event order rings a bell. I've done a quick web search and Chip Pearson has some nice stuff covering this in Excel on http://www.cpearson.com/ but judging by his material there is no 'validation event' as such. As Wassim pointed out it can be handled by failing the change event in code.

    The intriguing thing is that the Excel data validation appears to take place before the calculation or the change events (as checked in Pearson's example) but the actual calculation of the column on the DVCategory list is carried out first (before validation and before raising either event).

    Furthermore the calculate event precedes the change event suggesting that the intent of the change event is not to permit validation of the data but merely to notify or act on the change in some way.

    There should really be a 'BeforeChange' event like there is in Access to allow validation and an 'AfterChange' event to allow notification. Even without those, I'd have thought Excel would at least make its own internal validation code logical and run it before making the change and carrying out calculations on what may be invalid data.

    At this point I'll put this down as a quirky inconsistency of Excel and code the message directly.

    Cheers,
    Paul

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

    Re: Unique values but no warnings with Data validation (Excel 2000)

    I can't find out if the bug in Excel 97 was fixed in Excel 2000. In Excel 97 the change event was not triggered when data was entered using data validation from a list. There is a workaround for this, using the calculation event. see <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=88840&page=&view= &sb=&o=&vc=1>this post</A>. Hope this helps.

  8. #8
    New Lounger
    Join Date
    Sep 2001
    Location
    UK
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique values but no warnings with Data validation (Excel 2000)

    Thanks Hans,

    The calculate event is only invoked if there is a formula on the worksheet in Excel 2000. I guess that is a feature.

    The bug in 2000 (Microsoft might call it a feature too) can be seen if you set validation to a small list of numbers on a cell (e.g. restrict $A$1 to the list 1,2,3) and put a formula using that cell in another cell on the same sheet (e.g. set $B$1 = 2*$A$1).

    If you set $A$1 to an illegal value (e.g 4) you will see the value of $B$1 computed on the 'illegal' value even though the message comes up, you are prevented from completing the entry and the calculated and change events never get invoked.

    Cheers,
    Paul

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

    Re: Unique values but no warnings with Data validation (Excel 2000)

    Paul,

    Maybe I don't understand you correctly, but the trick might be to put a formula somewhere in a hidden place, just counting e.g. COUNTIF(A:A,"=1") which get changed if you change the contents of a cell in column A. I explained this in the attachment of the post I was referring to. When the 'countif' cell changes, the calculation event is triggered and you can do your validation.
    I tried the situation you explained and the calculate event is triggered, even if you enter an invalid number.

  10. #10
    New Lounger
    Join Date
    Sep 2001
    Location
    UK
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique values but no warnings with Data validation (Excel 2000)

    Its OK, I can use the change event for the message.

    It is a shame the message system provided with the data validation can't be used in this case.

    Furthermore, were I to write any code that would break badly on invalid data and then use data validation to prevent invalid data from being entered onto the sheet - the code may still be called with the invalid data.

    I can work around it but I was hoping there was a cleaner solution. I don't like kludges.

    Thanks for your help.

    Cheers,
    Paul

Posting Permissions

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