Detecting Worksheet change
 

Someone provided the following code that automatically detects a
change in a particular cell. It works great.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C6")) Is Nothing Then
Summary
End If
End Sub


I now need code to automatically detect if a change has taken place
ANYWHARE on a worksheet. Also, if I have 30 sheets and I need the
code for 26 of the 30 sheets, where do I place the code?

Your help is greatly appreciated.

Detecting Worksheet change
 

Hi Tim

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Hi"
End Sub

This will work on every cell change in the worksheet Tim
You can place this code in each Sheet Module where you want it to work

Or this one in the Thisworkbook module
This way you have one Event instead of 26
Fill in the index numbers from the sheets where the event
must not work " Case 1, 3 , 5 , 7 "



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Number = Sh.Index
Select Case Number
Case 1, 3 , 5 , 7
' do nothing
Case Else
MsgBox ""
End Select
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tim" wrote in message om...
Someone provided the following code that automatically detects a
change in a particular cell. It works great.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C6")) Is Nothing Then
Summary
End If
End Sub


I now need code to automatically detect if a change has taken place
ANYWHARE on a worksheet. Also, if I have 30 sheets and I need the
code for 26 of the 30 sheets, where do I place the code?

Your help is greatly appreciated.

Detecting Worksheet change
 

Thanks Ron. It works great. Your help is greatly appreciated!

"Ron de Bruin" wrote in message ...
Hi Tim

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Hi"
End Sub

This will work on every cell change in the worksheet Tim
You can place this code in each Sheet Module where you want it to work

Or this one in the Thisworkbook module
This way you have one Event instead of 26
Fill in the index numbers from the sheets where the event
must not work " Case 1, 3 , 5 , 7 "



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Number = Sh.Index
Select Case Number
Case 1, 3 , 5 , 7
' do nothing
Case Else
MsgBox ""
End Select
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tim" wrote in message om...
Someone provided the following code that automatically detects a
change in a particular cell. It works great.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C6")) Is Nothing Then
Summary
End If
End Sub


I now need code to automatically detect if a change has taken place
ANYWHARE on a worksheet. Also, if I have 30 sheets and I need the
code for 26 of the 30 sheets, where do I place the code?

Your help is greatly appreciated.