Worksheet Change event

D

DoctorG

I want to execute a specific routine whenever a cell of a certain column
changes.

All is OK if I press enter and the cursor is programmed to stay in the cell.
Then I check the ActiveCell.Column property and execute the code. I have a
problem if the user leaves the cell i.e. with a right or left arrow and the
active column upon execution of the Worksheet Change routine is different to
the one I wish to monitor.

Is there a way to know the address of the cell that triggered the Change
Event instead of the current/active cell ?
 
D

Dave Peterson

You could use the Worksheet_change event and Target.

Target will tell you what cell was just changed by the user:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:a100")) Is Nothing Then Exit Sub

Msgbox "User changed: " & target.address

End Sub

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm
 
B

Bob Phillips

Use Target.column, not activecell. I assume that you are using the worksheet
change event.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Top