Results 1 to 12 of 12
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Watching for Invalid Values (2000/SP3)

    I seem to remember something where I could watch a range of cells and when data is entered in them would trigger an event to see if the right thing was entered. Think it was done as a Private Sub Worksheet_Change().

    My customer has a column set aside with Validation to get 1 of 2 answers. If the answer is "No," we want to throw an error message if they try to key something into the D, E, or F columns on that row.

    I think I left my "smart cap" at home. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Watching for Invalid Values (2000/SP3)

    You don't need VBA code for this, you can do it with validation too.
    - Select columns D:F (or the range within these columns you want to apply the validation too)
    - Select Data | Validation...
    - In the 'Allow' dropdown list in the first tab, select Custom.
    - in the Formula list, enter this formula (assuming that D1 is the active cell in the selection):
    <pre>=OR($A1="Yes",ISEMPTY(D1))
    </pre>

    - In the third tab, make sure that the check box is ticked, that the Stop style is selected and that a title and text for the error message have been entered.
    - Click OK.

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Watching for Invalid Values (2000/SP3)

    Does not work! Entering this into the Validation dialog throws me an error that the <font color=red>"named range that you specified cannot be found."</font color=red>

    Also, this is backwards of what I am trying to accomplish for my customer. He was going to put sets of Option Buttons all down a column until I showed him Validation with a Yes/No choice.

    Next he wants to Warn someone typing in the corresponding cells in columns D,E, & F if the answer is Yes so they can fix their mistake That is why I wanted to have a module to watch for keystrokes.
    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Watching for Invalid Values (2000/SP3)

    Sorry, wrong translation (I didn't check it). It should have been ISBLANK instead of ISEMPTY. See attached workbook.

    As far as I know, there is no keystroke event in Excel. The Worksheet Change event occurs after a user has edited a cell, not while (s)he is editing it.

  5. #5
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Watching for Invalid Values (2000/SP3)

    That is what I want. The event after the cell is edited.
    Alan

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Watching for Invalid Values (2000/SP3)

    Your original request was
    <hr>If the answer is "No," we want to throw an error message if they try to key something into the D, E, or F columns on that row.<hr>
    The corrected validation I suggested does that (in the workbook I posted only for column D, but it can be extended to E and F very easily.) Apparently that is not what you want after all. Can you try to state as clearly as you can what you do want?

  7. #7
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Watching for Invalid Values (2000/SP3)

    Do what I mean, not what I say!

    The default on column C which is all set as a Yes/No validation is No. We want to check as the same row has data entered in columns D,E, & F what the value is in C. I think this is an on_change type event but I do not know the syntax.
    Alan

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Watching for Invalid Values (2000/SP3)

    Please try to be specific.
    <hr>We want to check as the same row has data entered in columns D,E, & F what the value is in C.<hr>
    This only says you want to check something, but not what you want to do as a result of this check. (I know you have told us before what you wanted, but you didn't like the solution, so I must ask again.)

  9. #9
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Watching for Invalid Values (2000/SP3)

    I don't think I know what I want. I'll try something else later.
    Alan

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

    Re: Watching for Invalid Values (2000/SP3)

    As far as I can tell, Hans' solution using data valadation does exactly what you have been describing. Have you actually tried his workbook?

    If you don't like his solution, the alternate would be to use the worksheet change event routine. You will either have to code that yourself, or tell us how what you are asking for is different from what Hans has already done.
    Legare Coleman

  11. #11
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Watching for Invalid Values (2000/SP3)

    I tried Hans' solution but it is not what I am looking for. I want to use the Worksheet_Change event.

    After (not during) the user enters data in either D, E, or F, I want the spreadsheet to look to see what the status of C is and either allow the data or put up a msgbox box that they need to review C.
    Alan

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

    Re: Watching for Invalid Values (2000/SP3)

    The only difference I can see between what Hans provided and what you are describing is:

    1- Hans had the Yes/No answer in column A not C since you had previously not specified what column this was in.

    2- Hans solution was only in column D and did not include E and F.

    I have attached Hans' workbook modified to fix the above two differences. If this is still not what you want, then we really need a description of what you want that is different. It is really hard to code something when you don't know what it is.
    Legare Coleman

Posting Permissions

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