Fire Event only when Cell Change?

[HotRod]

I had some code that I was working with the other day that would fire an
event only when the contents of the cell changed, any ideas? I'm currently
using the Worksheet_SelectionChange() but it fires every time I leave a cell
not only when the contents are changed.

 

[JulieD]

Hi

use Worksheet_Change

check out http://www.cpearson.com/excel/events.htm for more info

 

[Chip Pearson]

Don't use the SelectionChange event. Use the Change event.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[HotRod]

How come these EVENTS aren't listed in the drop down lists?

By the way it was

Sub Worksheet_Change(ByVal Target As Range)

that I was thinking of.

 

[Tom Ogilvy]

They are listed in the dropdowns for the class modules where they are
defined.

 

[Chip Pearson]

They are listed in the drop down. Change the left drop down from
"(General)" to "Worksheet", and the worksheet events are listed
in the right side drop down.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[HotRod]

In the code when I fire the Worksheet_SelectionChange() Event I then
reformat the contents of a cell, this in turn re-fires the Event producing
an infinite loop. Is there something I can do, or do I need to create a
global variable to track this problem?

 

[Tom Ogilvy]

You need to learn how to program without selecting

Instead of

Range("A1").Select
Selection.Numberformat = "#,##0.00"

do

Range("A1").Numberformat = "#,##0.00"

no selection performed; no event triggered.