Worksheet Change Event

[Damo10]

Hi,

I have some data validation in column AD which the user selects an item form a drop down list, I also have a worksheet change event which checks the formula result in column AG, this all works fine when the user selects the item from the drop down list however if the user types the item in the cell the worksheet change does not work. Is there a way around this?

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("AD:AD")) Is Nothing Then
 Exit Sub
 Else
 Dim x As Long
 Dim c As Range
 x = ActiveCell.Row
 Set c = Range("AD" & x).Offset(0, 3)
 If c.Text = "Error" Then
 MsgBox Chr(10) & Chr(10) & ("Tank Selection Error                    ") & Chr(10) & Chr(10) & "Please Check Selection" & Chr(10) & Chr(10), vbCritical, "Selection Error"
 With Application
 .Undo
 End With
 Exit Sub
 Else
 End If
End If

Regards

 

[Andrew Poulsom]

The Worksheet_Change event will fire whether the user types an entry in the cell or selects from a data validation dropdown. What makes you think that it isn't firing?

 

[Damo10]

Hi,

I have tested it several times and all the formulas work and all expected results in the other cell are correct but it just dosnt fire the change event when the item is typed int the cell, it only works when the item is selected from the data validation drop down list.

Regards

 

[AlphaFrog]

Hi,

I have some data validation in column AD which the user selects an item form a drop down list, I also have a worksheet change event which checks the formula result in column AG, this all works fine when the user selects the item from the drop down list however if the user types the item in the cell the worksheet change does not work. Is there a way around this?

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("AD:AD")) Is Nothing Then
 Exit Sub
 Else
 Dim x As Long
 Dim c As Range
 x = ActiveCell.Row
 Set c = Range("AD" & x).Offset(0, 3)
 If c.Text = "Error" Then
 MsgBox Chr(10) & Chr(10) & ("Tank Selection Error                    ") & Chr(10) & Chr(10) & "Please Check Selection" & Chr(10) & Chr(10), vbCritical, "Selection Error"
 With Application
 .Undo
 End With
 Exit Sub
 Else
 End If
End If

Regards

When the user selects from the drop down list, the active cell is the cell with the drop down list. When the user types a value in a cell, they probably hit ENTER. The Active cell is the cell below the cell they just type in.

 

[Andrew Poulsom]

Which version of Excel are you using? I don't have access to 2007 or 2010 where I am at the moment, but I can't reproduce your problem in Excel 2003. Are you sure that events are enabled at all times?

 

[Damo10]

Thanks AlphaFrog that makes sense, is there a way around this issue?

 

[AlphaFrog]

x = Target.Row

Target is the cell(s) that were changed.

 

[Damo10]

Great that works, thankyou both for your time & help

Regards