Sorting and the Worksheet Change Event
 

I am monitoring changes to cell B9 with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
MsgBox ("B9 has changed")
End Sub

This works if I type a value in B9 or paste a value in B9.

If I sort column B and B9 changes, the event is not triggered.

How can I trap this type of change?
--
Gary''s Student - gsnu2007xx

Sorting and the Worksheet Change Event
 

Fully explain what you are trying to do.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary''s Student" wrote in message
...
I am monitoring changes to cell B9 with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
MsgBox ("B9 has changed")
End Sub

This works if I type a value in B9 or paste a value in B9.

If I sort column B and B9 changes, the event is not triggered.

How can I trap this type of change?
--
Gary''s Student - gsnu2007xx

Sorting and the Worksheet Change Event
 

One of our people has a spreadsheet with a large table that is frequently
updated and sorted. If certain cells in column B are changed as a result of
the sort, the changes need to be logged and other reports generated.

The way things are right now, the user performs the sort and then manually
checks each of the key cells (about 20 of them). If any of them has changed,
the user starts the macro. Sadly the user occasionally misses a change.

My original suggestion was to have a Worksheet change event macro monitor
the key cells and start of chain of events.

My suggestion was no good because the cells change without the event macro
detecting it.

You have good practical solutions to practical problems. What would you do??
--
Gary''s Student - gsnu2007xx


"Don Guillett" wrote:

Fully explain what you are trying to do.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary''s Student" wrote in message
...
I am monitoring changes to cell B9 with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
MsgBox ("B9 has changed")
End Sub

This works if I type a value in B9 or paste a value in B9.

If I sort column B and B9 changes, the event is not triggered.

How can I trap this type of change?
--
Gary''s Student - gsnu2007xx

Sorting and the Worksheet Change Event
 

Why not build in the check, sort and logging into a single macro. The idea
would be to store the values in the cells you are interested in variables or
an array when the macro starts; next, perform the sort through code; and
then loop through the cells you are interested in and compare their current
values with the stored values... if they differ, write whatever you need to
into your log file.

Rick


"Gary''s Student" wrote in message
...
One of our people has a spreadsheet with a large table that is frequently
updated and sorted. If certain cells in column B are changed as a result
of
the sort, the changes need to be logged and other reports generated.

The way things are right now, the user performs the sort and then manually
checks each of the key cells (about 20 of them). If any of them has
changed,
the user starts the macro. Sadly the user occasionally misses a change.

My original suggestion was to have a Worksheet change event macro monitor
the key cells and start of chain of events.

My suggestion was no good because the cells change without the event macro
detecting it.

You have good practical solutions to practical problems. What would you
do??
--
Gary''s Student - gsnu2007xx


"Don Guillett" wrote:

Fully explain what you are trying to do.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary''s Student" wrote in
message
...
I am monitoring changes to cell B9 with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
MsgBox ("B9 has changed")
End Sub

This works if I type a value in B9 or paste a value in B9.

If I sort column B and B9 changes, the event is not triggered.

How can I trap this type of change?
--
Gary''s Student - gsnu2007xx

Sorting and the Worksheet Change Event
 

Thank you Don for getting me to re-think the problem!
Thank you Rick for a better integrated solution!
--
Gary''s Student - gsnu200800


"Rick Rothstein (MVP - VB)" wrote:

Why not build in the check, sort and logging into a single macro. The idea
would be to store the values in the cells you are interested in variables or
an array when the macro starts; next, perform the sort through code; and
then loop through the cells you are interested in and compare their current
values with the stored values... if they differ, write whatever you need to
into your log file.

Rick


"Gary''s Student" wrote in message
...
One of our people has a spreadsheet with a large table that is frequently
updated and sorted. If certain cells in column B are changed as a result
of
the sort, the changes need to be logged and other reports generated.

The way things are right now, the user performs the sort and then manually
checks each of the key cells (about 20 of them). If any of them has
changed,
the user starts the macro. Sadly the user occasionally misses a change.

My original suggestion was to have a Worksheet change event macro monitor
the key cells and start of chain of events.

My suggestion was no good because the cells change without the event macro
detecting it.

You have good practical solutions to practical problems. What would you
do??
--
Gary''s Student - gsnu2007xx


"Don Guillett" wrote:

Fully explain what you are trying to do.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary''s Student" wrote in
message
...
I am monitoring changes to cell B9 with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
MsgBox ("B9 has changed")
End Sub

This works if I type a value in B9 or paste a value in B9.

If I sort column B and B9 changes, the event is not triggered.

How can I trap this type of change?
--
Gary''s Student - gsnu2007xx