Worksheet Change Event question

[Guest]

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

 

[Guest]

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.

 

[Bob Phillips]

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)

 

[(PeteCresswell)]

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.

 

[Guest]

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