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
> >