Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event question
Hello,
I'm using Excel 2003. The woksheet change event fires when any data on the worksheet changes. Does anyone know a way to determine which specifc cell (or range I suppose) has changed? Thanks, Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event question
If you are working with (changing) only cells with numeric, you can make a
copy of your original sheet to another sheet in the workbook which contains ONLY VALUES from your original sheet. In a third sheet, in every cell, just have a formula that subtracts the cell value of the original sheet from the respective cell value of the updated sheet. Any cells that are now not equal to 0 in your third sheet, you know have changed. "scottydel" wrote: Hello, I'm using Excel 2003. The woksheet change event fires when any data on the worksheet changes. Does anyone know a way to determine which specifc cell (or range I suppose) has changed? Thanks, Scott |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event question
If you use the Workseheet_Change event, Excel passes the changed range to
the evnt, which is tested as the Target argument. This is a skeleton example of code to test and handle this Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target ' do your stuff End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "scottydel" wrote in message ... Hello, I'm using Excel 2003. The woksheet change event fires when any data on the worksheet changes. Does anyone know a way to determine which specifc cell (or range I suppose) has changed? Thanks, Scott |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event question
Per scottydel:
I'm using Excel 2003. The woksheet change event fires when any data on the worksheet changes. Does anyone know a way to determine which specifc cell (or range I suppose) has changed? I haven't done a lot of this, but in what I've done I've built a bunch of module-level constants that identified each cell/row that I was concerned with and then cased out on Target.Row and Target.Column. -- PeteCresswell |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event question
Thanks for the replies. Bob I used your sample code, and am having some
issues. I have the following code, which is supposed to copy data from a cell on one sheet to a corresponding cell on another sheet (shifted left one column and up one row on the sheet I am pasting to). My code works sometimes, and sometimes has no effect at all (setting a break-point doesn't even step in to the code). Pretty confused. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_Exit Excel.Application.EnableEvents = False Target.Copy UnProtectSheet "Master Data" Worksheets("Master Data").Cells(Target.Row - 1, Target.Column - 1).PasteSpecial xlPasteValues, _ xlPasteSpecialOperationNone, _ False, _ False Target.Select ProtectSheet "Master Data" ws_Exit: Excel.Application.EnableEvents = True End Sub "Bob Phillips" wrote: If you use the Workseheet_Change event, Excel passes the changed range to the evnt, which is tested as the Target argument. This is a skeleton example of code to test and handle this Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target ' do your stuff End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "scottydel" wrote in message ... Hello, I'm using Excel 2003. The woksheet change event fires when any data on the worksheet changes. Does anyone know a way to determine which specifc cell (or range I suppose) has changed? Thanks, Scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quick VBA Worksheet Change Event or Selection Question: | Excel Worksheet Functions | |||
Event Change Question | Excel Programming | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming |