Differing Results From Data Validation Entry

[Mr Nick]

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.

 

[Mr Nick]

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.

 

[Mr Nick]

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

 

[Mr Nick]

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