I use a data validation to fill a cell from a list. This doesn't appear to
trigger a cell.value change event however like typing into the cell does. Is
there a way around this?

In xl2000 and later it does. In Excel 97, it will if you have a hand
entered list. If not, the best you can do is have a formula refer to that
cell and react in the calculate event - however, it is not limited to
changes in that cell.

--
Regards,
Tom Ogilvy

"myleslawrence" wrote in message
...
I use a data validation to fill a cell from a list. This doesn't appear to
trigger a cell.value change event however like typing into the cell does.
Is
there a way around this?

I use this to determine if a cell has changed:
if Not Application.Intersect(ActiveCell,Range("F6:F22")) is Nothing then
KeyCellsChanged

KeyCellsCahanged never gets called when I use the cell validate to populate
the cell but works fine if I type a value into the cell.
Am I doing something wrong?


"Tom Ogilvy" wrote in message
...
In xl2000 and later it does. In Excel 97, it will if you have a hand
entered list. If not, the best you can do is have a formula refer to
that
cell and react in the calculate event - however, it is not limited to
changes in that cell.

--
Regards,
Tom Ogilvy

"myleslawrence" wrote in message
...
I use a data validation to fill a cell from a list. This doesn't appear
to
trigger a cell.value change event however like typing into the cell does.
Is
there a way around this?

Hi Myles,

What version of Excel are you using?

As Tom pointed out, in versions after xl97 data validation does trigger a
change event.

if Not Application.Intersect(ActiveCell,Range("F6:F22")) is Nothing then
KeyCellsChanged

In this event code you would be advised to use Target rather than
ActiveCell, the two are not synonymous. As a simple example, in the above
code, add a
MsgBox "Changed!"
make an entry in cell F5 and confirm with the Enter key.


---
Regards,
Norman



"myleslawrence" wrote in message
...
I use this to determine if a cell has changed:
if Not Application.Intersect(ActiveCell,Range("F6:F22")) is Nothing then
KeyCellsChanged

KeyCellsCahanged never gets called when I use the cell validate to
populate the cell but works fine if I type a value into the cell.
Am I doing something wrong?


"Tom Ogilvy" wrote in message
...
In xl2000 and later it does. In Excel 97, it will if you have a hand
entered list. If not, the best you can do is have a formula refer to
that
cell and react in the calculate event - however, it is not limited to
changes in that cell.

--
Regards,
Tom Ogilvy

"myleslawrence" wrote in message
...
I use a data validation to fill a cell from a list. This doesn't appear
to
trigger a cell.value change event however like typing into the cell
does.
Is
there a way around this?

Thanks Norman but I guess I'm not following you. Replace the variable
ActiveCell with Target? This generates an error.
"Norman Jones" wrote in message
...
Hi Myles,

What version of Excel are you using?

As Tom pointed out, in versions after xl97 data validation does trigger a
change event.

if Not Application.Intersect(ActiveCell,Range("F6:F22")) is Nothing then
KeyCellsChanged

In this event code you would be advised to use Target rather than
ActiveCell, the two are not synonymous. As a simple example, in the above
code, add a
MsgBox "Changed!"
make an entry in cell F5 and confirm with the Enter key.


---
Regards,
Norman



"myleslawrence" wrote in message
...
I use this to determine if a cell has changed:
if Not Application.Intersect(ActiveCell,Range("F6:F22")) is Nothing then
KeyCellsChanged

KeyCellsCahanged never gets called when I use the cell validate to
populate the cell but works fine if I type a value into the cell.
Am I doing something wrong?


"Tom Ogilvy" wrote in message
...
In xl2000 and later it does. In Excel 97, it will if you have a hand
entered list. If not, the best you can do is have a formula refer to
that
cell and react in the calculate event - however, it is not limited to
changes in that cell.

--
Regards,
Tom Ogilvy

"myleslawrence" wrote in message
...
I use a data validation to fill a cell from a list. This doesn't appear
to
trigger a cell.value change event however like typing into the cell
does.
Is
there a way around this?

Norman is assuming you are using the Change event


Private Sub Worksheet_Change(ByVal Target As Range)
if Not Application.Intersect(Target,Range("F6:F22")) is Nothing then
msgbox "Change is in the range "F6:F22")
End if
End Sub

Placed in the module of the worksheet where you want this behavior (right
click on the sheet tab, select view code).

--
Regards,
Tom Ogilvy

"myleslawrence" wrote in message
...
Thanks Norman but I guess I'm not following you. Replace the variable
ActiveCell with Target? This generates an error.
"Norman Jones" wrote in message
...
Hi Myles,

What version of Excel are you using?

As Tom pointed out, in versions after xl97 data validation does trigger
a
change event.

if Not Application.Intersect(ActiveCell,Range("F6:F22")) is Nothing
then
KeyCellsChanged

In this event code you would be advised to use Target rather than
ActiveCell, the two are not synonymous. As a simple example, in the
above
code, add a
MsgBox "Changed!"
make an entry in cell F5 and confirm with the Enter key.


---
Regards,
Norman



"myleslawrence" wrote in message
...
I use this to determine if a cell has changed:
if Not Application.Intersect(ActiveCell,Range("F6:F22")) is Nothing
then
KeyCellsChanged

KeyCellsCahanged never gets called when I use the cell validate to
populate the cell but works fine if I type a value into the cell.
Am I doing something wrong?


"Tom Ogilvy" wrote in message
...
In xl2000 and later it does. In Excel 97, it will if you have a hand
entered list. If not, the best you can do is have a formula refer to
that
cell and react in the calculate event - however, it is not limited to
changes in that cell.

--
Regards,
Tom Ogilvy

"myleslawrence" wrote in message
...
I use a data validation to fill a cell from a list. This doesn't
appear
to
trigger a cell.value change event however like typing into the cell
does.
Is
there a way around this?

Never mind, I got it and thanks very much.
"Norman Jones" wrote in message
...
Hi Myles,

What version of Excel are you using?

As Tom pointed out, in versions after xl97 data validation does trigger a
change event.

if Not Application.Intersect(ActiveCell,Range("F6:F22")) is Nothing then
KeyCellsChanged

In this event code you would be advised to use Target rather than
ActiveCell, the two are not synonymous. As a simple example, in the above
code, add a
MsgBox "Changed!"
make an entry in cell F5 and confirm with the Enter key.


---
Regards,
Norman



"myleslawrence" wrote in message
...
I use this to determine if a cell has changed:
if Not Application.Intersect(ActiveCell,Range("F6:F22")) is Nothing then
KeyCellsChanged

KeyCellsCahanged never gets called when I use the cell validate to
populate the cell but works fine if I type a value into the cell.
Am I doing something wrong?


"Tom Ogilvy" wrote in message
...
In xl2000 and later it does. In Excel 97, it will if you have a hand
entered list. If not, the best you can do is have a formula refer to
that
cell and react in the calculate event - however, it is not limited to
changes in that cell.

--
Regards,
Tom Ogilvy

"myleslawrence" wrote in message
...
I use a data validation to fill a cell from a list. This doesn't appear
to
trigger a cell.value change event however like typing into the cell
does.
Is
there a way around this?