Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Differing Results From Data Validation Entry

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Manchester, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a sheet which uses data validation to enter values into my sheet.

    My problem is that if I type the data validation list as numeric values in the source box, all my formulas work OK. However if I use values from a range of cells (which is the way I need to do it for the sheet operate as required) then the values are not recognised by the sheet unless once the data is entered, I then re-highlight the cell and hit ENTER.

    This is not acceptable behaviour and I need to figure out how to overcome this limitation. I'm using Excel 97 if that makes any difference.

    Thanks in anticipation of a working outcome.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Manchester, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The data validation list is compiled correctly from the cell range. It is when I use the list to enter the value into the cell i.e 10, my formula that is looking for the value in the validated cell does not see the 10 unless I hit enter after re-highlighting or unless I put numeric values in the source box.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Manchester, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Quick Example:
    If I use the following code and use the different methods, one pops up a box, the other doesn't.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If [a1] = 3 Then
    MsgBox "Hello"
    End If
    End Sub

    [ This Message was edited by: Mr Nick on 2002-03-20 08:47 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Manchester, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A bit more info.

    The data from the validated cell IS recognised by formulas in cells but not by worksheet macros. Is this a shortfall in Excel or am I doing something wrong?

    [ This Message was edited by: Mr Nick on 2002-03-21 07:10 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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