Cell validation

[myleslawrence]

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?

 

[Tom Ogilvy]

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.

 

[myleslawrence]

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?

 

[Norman Jones]

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.

 

[myleslawrence]

Thanks Norman but I guess I'm not following you. Replace the variable
ActiveCell with Target? This generates an error.

 

[Tom Ogilvy]

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).

 

[myleslawrence]

Never mind, I got it and thanks very much.

Hi Myles,

What version of Excel are you using?

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


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.