Detecting Worksheet change

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

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.
 
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
 
Another way to monitor a single cell's change...

'detect a change in A1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
MsgBox "A1 has changed."
End If
End Sub
 
Back
Top