How to be notified when user changes cell value in selected column?

[Jack]

When user changes the cell value in selected column I would like to do some
recalculation.
How to get such notification?
I am using vbasic and Excel automation.
Jack

 

[Guest]

In the change event of the Worksheet object, you'll need something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then
MsgBox Target.Address & " was changed"
End If
End Sub

 

[Jack]

It is not what I need.
I need something to tell me that user has edited cell in selected column.
Jack

 

[Guest]

Jack,

The Worksheet_Change event fires whenever a cell value is modified. Wether
you're looking for immediate notification or not, I believe it is where you
will need to put your code. The example I gave gives immediate notification
to the user whenever a cell is modified in column I. If you don't need
immediate notification, you can use a Collection Object or an array to 'keep
track' of cells that were modified by putting such code in the
Worksheet_Change event..

Now, if what you mean by 'tell me that user has edited cell in selected
column' is that you send a spreadsheet to someone and when you get it back,
you want to know which cells were modified. Then, check out Excel's feature
for this. Tools->Track Changes->Highlight Changes.

 

[Jack]

Thank you for following that up.
I have experimented with that and I have found that:
moExcelApp_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.Range)
works for me perfectly.

I do not understand why your code does not work for me.
Maybe because I test with only one sheet opened?
What do you think?

I still am confused about the 'workbook' versus 'sheet' concept.
Jack

 

[Guest]

Jack,

Not sure what moExcelApp is, but from the looks of your code, it looks like
it's the SheetChange event handler for the workbook object. If so, then that
event also fires, right after the change event for the sheet.

One way to understand the concept is to do what you have been doing -
experiment with it.

Try this:

1. Create a new workbok.
2. In the ThisWorkbook code module, paste this code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox Target.Address & " was changed " & Sh.Name, vbInformation,
"Message from ThisWorkbook"
End Sub


3. In the Sheet1 code module, paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address & " was changed in Sheet1", vbInformation,
"Message from Sheet1"
End Sub


Now, edit any cell in Sheet1. You'll see both events fire. The one in
Sheet1 first, followed by the one in ThisWorkbook. Edit any cell in Sheet2.
The Sheet1 change event doesn't fire but the one in the workbook does. So,
if you need your code to apply to all sheets in the workbook, you need to
trap the workbook_sheetchange event. If all you nee to keep track of is one
sheet, do it in the worksheet_change event. Hope that helps.