I know its possible to put change events in individual sheets in a file. But
if I want to trigger a macro based on changes in every sheet in a file. For
example, the code below tracks the changes to A1 on a sheet that are greater
than 100. What if I wanted to track this same outcome but on all sheets in a
workbook. Can you do this without pasting the same change event code snippet
in each sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target 100 Then
MsgBox ("Changed Value")
End If
End Sub

Thanks
EM

There is an event in ThisWorkbook that catches changes in any sheet...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
msgBox sh.name & " - " & target.address
End Sub
--
HTH...

Jim Thomlinson


"ExcelMonkey" wrote:

I know its possible to put change events in individual sheets in a file. But
if I want to trigger a macro based on changes in every sheet in a file. For
example, the code below tracks the changes to A1 on a sheet that are greater
than 100. What if I wanted to track this same outcome but on all sheets in a
workbook. Can you do this without pasting the same change event code snippet
in each sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target 100 Then
MsgBox ("Changed Value")
End If
End Sub

Thanks
EM

There is an event in ThisWorkbook that catches changes in any sheet...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
msgBox sh.name & " - " & target.address
End Sub
--
HTH...

Jim Thomlinson


"ExcelMonkey" wrote:

I know its possible to put change events in individual sheets in a file. But
if I want to trigger a macro based on changes in every sheet in a file. For
example, the code below tracks the changes to A1 on a sheet that are greater
than 100. What if I wanted to track this same outcome but on all sheets in a
workbook. Can you do this without pasting the same change event code snippet
in each sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target 100 Then
MsgBox ("Changed Value")
End If
End Sub

Thanks
EM

Perfect. Thanx.

EM

"Jim Thomlinson" wrote:

There is an event in ThisWorkbook that catches changes in any sheet...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
msgBox sh.name & " - " & target.address
End Sub
--
HTH...

Jim Thomlinson


"ExcelMonkey" wrote:

I know its possible to put change events in individual sheets in a file. But
if I want to trigger a macro based on changes in every sheet in a file. For
example, the code below tracks the changes to A1 on a sheet that are greater
than 100. What if I wanted to track this same outcome but on all sheets in a
workbook. Can you do this without pasting the same change event code snippet
in each sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target 100 Then
MsgBox ("Changed Value")
End If
End Sub

Thanks
EM

So the original macro tracked changes to a given worksheet based on two events:

1) Private Sub Worksheet_Change(ByVal Target As Range)
Tracked infromation
2) Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Kept track of old info by passing old cell contents to Public variable


Now the new one trackes changes in all worksheets.
1) Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

But how doe I keep track of the old cell contents through the public
variable. That is
Worksheet_SelectionChange event went in an individual sheet before. Now I
need to track this in all sheets.

Thanks

EM

"Jim Thomlinson" wrote:

There is an event in ThisWorkbook that catches changes in any sheet...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
msgBox sh.name & " - " & target.address
End Sub
--
HTH...

Jim Thomlinson


"ExcelMonkey" wrote:

I know its possible to put change events in individual sheets in a file. But
if I want to trigger a macro based on changes in every sheet in a file. For
example, the code below tracks the changes to A1 on a sheet that are greater
than 100. What if I wanted to track this same outcome but on all sheets in a
workbook. Can you do this without pasting the same change event code snippet
in each sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target 100 Then
MsgBox ("Changed Value")
End If
End Sub

Thanks
EM