Worksheet Change event

[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 ?

[DoctorG]

Sorry for posting this in the General section instead of the Programming
one....

[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

DoctorG wrote:
>
> 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 ?

--

Dave Peterson

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

"DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
news:94F58163-BB05-4CCB-B3F6-090F2A2B2313@microsoft.com...
> 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 ?

[DoctorG]

Sorry for posting this in the General section instead of the Programming
one....

[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

DoctorG wrote:
>
> 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 ?

--

Dave Peterson

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

"DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
news:94F58163-BB05-4CCB-B3F6-090F2A2B2313@microsoft.com...
> 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 ?

[DoctorG]

Thank you both!! You 've been most helpful.