Sorting and the Worksheet Change Event

[Gary''s Student]

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?

 

[Don Guillett]

Fully explain what you are trying to do.

 

[Gary''s Student]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

Thank you Don for getting me to re-think the problem!
Thank you Rick for a better integrated solution!