Change Event across every sheet in workbook
 

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

Change Event across every sheet in workbook
 

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

Change Event across every sheet in workbook
 

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

Change Event across every sheet in workbook
 

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

Change Event across every sheet in workbook
 

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