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

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

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

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

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