Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Detecting Calc On or Off | Excel Discussion (Misc queries) | |||
Detecting Duplicate Sums - Worksheet code | Excel Discussion (Misc queries) | |||
Cell value - detecting change | Excel Worksheet Functions | |||
Detecting absentees | Excel Discussion (Misc queries) | |||
Detecting a color | Excel Worksheet Functions |