Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
How to be notified when user changes cell value in selected column?
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How to be notified when user changes cell value in selected column
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How to be notified when user changes cell value in selected column
It is not what I need.
I need something to tell me that user has edited cell in selected column. Jack "Vergel Adriano" wrote in message ... 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How to be notified when user changes cell value in selected co
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" wrote: It is not what I need. I need something to tell me that user has edited cell in selected column. Jack "Vergel Adriano" wrote in message ... 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How to be notified when user changes cell value in selected co
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 "Vergel Adriano" wrote in message ... 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" wrote: It is not what I need. I need something to tell me that user has edited cell in selected column. Jack "Vergel Adriano" wrote in message ... 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How to be notified when user changes cell value in selected co
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. "Jack" wrote: 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 "Vergel Adriano" wrote in message ... 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" wrote: It is not what I need. I need something to tell me that user has edited cell in selected column. Jack "Vergel Adriano" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get notified of replies in this forum | Excel Discussion (Misc queries) | |||
Ho to get notified by getting mail when any changes done in excel | Excel Discussion (Misc queries) | |||
how can I sort on the column selected by the user? | Excel Programming | |||
How to highlight row and column of the selected cell | Excel Discussion (Misc queries) | |||
how to know the column/row of a selected cell in vba? | Excel Programming |