Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Data Validation List is Empty (2002)

    I’m using the Worksheet_Change event and I’m having trouble making the target (which happens to be a cell that is a data validation list) select the first item in the list whenever the user deletes the selected value? I’m trying to prevent empty or spaces from being entered in the cell. Or even better, to reselect the value in the list that the user deleted.
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Data Validation List is Empty (2002)

    You could use Application.Undo to restore the previously selected value.

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Validation List is Empty (2002)

    Thanks Hans! This works great if the user deletes the value. I’m having trouble handing the situation if the user blanks the cell out. The second IF statement below doesn’t meet the condition.

    If I use the Data Validation Error routine, I run into the problem if the user presses the Retry button and the entire list is missing. I’d rather capture it in the macro.

    Any idea what I’m doing wrong?

    This seems so simple that I must be brain dead today.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("TargetDate")) Is Nothing Then
    If Target = " " Then ‘not working
    MsgBox "You must select a date", Title:="Error Message", Buttons:=vbExclamation
    Application.Undo
    End If
    If IsEmpty(Target) Then ‘this works when the cell is deleted
    MsgBox "You must select a date", Title:="Error Message", Buttons:=vbExclamation
    Application.Undo
    End If
    End If
    End Sub
    </pre>

    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Data Validation List is Empty (2002)

    I'd use
    <code>
    If Range("TargetDate") = "" Then
    </code>
    Note that there is no space between the quotes <code>""</code>.

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Validation List is Empty (2002)

    I put no spaces between the quotes. When I blank the cell out it did not catch the condition. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Data Validation List is Empty (2002)

    What exactly do you mean by "blank the cell out"?

  7. #7
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Validation List is Empty (2002)

    The user can use the space bar to erase the data in the cell / list.
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Data Validation List is Empty (2002)

    That's not a good way to erase data, but try this:

    If Trim(Range("TargetDate")) = "" Then

  9. #9
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Validation List is Empty (2002)

    That's not a good way to erase data. I agree but unfortunately some do it. Your suggestion worked great. Thank you!

    I also noticed that if I use... If Trim(TARGET) = "" Then
    will also work. Which is better to use, TargetDate or the VBA supplied Target variable? Or does it not matter?
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Data Validation List is Empty (2002)

    Target could be more than one cell, for example if the user selects a range of cells and presses Delete. This could cause problems if you use Target - the value of a multi-cell range is undefined. Assuming that the TargetDate range is a single cell, it's better to use Range("TargetDate").

  11. #11
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Validation List is Empty (2002)

    I completely forgot about target being a multi-cell range. Thanks very much for the help Hans. Have a great day.
    You know it's time to diet when you push away from the table and the table moves.

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts

    Re: Data Validation List is Empty (2002)

    Just a thought but when you use Data Validation there is a checkbox labelled [ ]Ignore blank.

    It seems to me that you need to uncheck this setting.

    zeddy

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

    Re: Data Validation List is Empty (2002)

    If you clear the check box "Ignore Blank", Excel will display an error message when the user edits the cell and clears the value using backspace/delete. But the user can still clear the cell by selecting it and pressing Delete (i.e. without editing the cell).

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts

    Re: Data Validation List is Empty (2002)

    You are so correct.

    Bit of a waste of time that checkbox then

    zeddy

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

    Re: Data Validation List is Empty (2002)

    I've always wondered why they took the trouble to create it...

Page 1 of 2 12 LastLast

Posting Permissions

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