Differing Results From Data Validation Entry

Mr Nick · Mar 20th, 2002, 09:25 AM

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 · Mar 20th, 2002, 09:35 AM

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 · Mar 20th, 2002, 09:47 AM

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 · Mar 21st, 2002, 01:12 AM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 20th, 2002, 09:25 AM	#1
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	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.

Mar 20th, 2002, 09:35 AM	#2
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	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.

Mar 20th, 2002, 09:47 AM	#3
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	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 ]

Mar 21st, 2002, 01:12 AM	#4
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	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 ]