Excel 97 Event problem

[Guest]

Hi All;

I am facing one problem regarding the validation. I add in one simple function in Worksheet_Change events and also apply a validation list in a worksheet on cell “A1†and in the list got value 1 and 2. When user chooses 1 from the list, the system will prompt “The value is one†otherwise will prompt “The value is not oneâ€. This simple program can work properly on Excel 2000 & above, however it doesn’t work on Excel 97. I don’t know why using Excel 2000 & above, when change the value by mouse click in the cell A1 the corresponding result will prompt automatically. However it doesn’t works in Excel 97. What’s wrong for Excel 97? Anyone has got the idea?

This is the simple code what I have created.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Cells(1, 1).Value = 1 Then
MsgBox "The value is one"
Else
MsgBox "The value is not one"
End If

End Sub

Thanks & Regards

 

[Max]

FWIW, just tested in excel 97 (version used)
and there wasn't any problem ..

The correct msgbox prompt appeared automatically
when selection was made via DV

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hi All;

I am facing one problem regarding the validation. I add in one simple

function in Worksheet_Change events and also apply a validation list in a
worksheet on cell "A1" and in the list got value 1 and 2. When user chooses
1 from the list, the system will prompt "The value is one" otherwise will
prompt "The value is not one". This simple program can work properly on
Excel 2000 & above, however it doesn't work on Excel 97. I don't know why
using Excel 2000 & above, when change the value by mouse click in the cell
A1 the corresponding result will prompt automatically. However it doesn't
works in Excel 97. What's wrong for Excel 97? Anyone has got the idea?

 

[Frank Kabel]

Hi
in Excel 97 the worksheet_change event is not triggered by a change in
the validation list. You have to use a workaround in Excel 97. e.g.
- add a formula which just references this drop-down cell (e.g. =A1)
- you can hide this cell if you want
- now use the worksheet_calculate event and check within this dialog
the value of cell A1 (you may also check if A1 has been changed at all)

 

[Max]

Hi Frank,

I wasn't able to reproduce the OP's error in xl97, as per post.

Is there anything wrong which I did "right" unknowingly ? <g>

(The DV put in A1 is "List" with source: 1,2)

 

[Frank Kabel]

Hi
in my Excel 97 version i does not work. Source is for me a different
ange but I doubt this is the reason

 

[Max]

Thanks, Frank.
It'll remain one of the many quaint mysteries of Excel (97?) then ... <g>

 

[Max]

Thanks, Frank
It'll remain one of the many quaint mysteries of Excel (97?), then .. <g>

 

[Debra Dalgleish]

A delimited list will trigger the change event in Excel 97. There's a
note on Excel 97 behaviour here:

http://www.contextures.com/xlDataVal08.html#Change

 

[Max]

Debra, thanks !

So .. that's what I did "right" which made it work in xl97
(i.e. typed the list items in the DV dialog)

Scratch one quaint mystery then ... <g>

 

[Frank Kabel]

Hi Debra
thanks for this info.