tracking updates
 

I am looking for a simple way to track when pricing on a worksheet has been
updated. I would like to have one cell contain the update date and if
possible, the username. Any cell on the worksheet may be changed and all I
want is the cell updated on the worksheet if something on it changes. Then
that date would remain until something else on the worksheet changes.
Formula or macro doesn't matter, but would prefer one that keeps file size
smaller. I will have several worksheets in a workbook, and each sheet will
have its own date.

Separately, I would also like to have the master worksheet in the file show
a "special pricing" message if the pricing from another worksheet is changed
after the file has been opened.

thanks, Pam

tracking updates
 

Pam
Here is something to get this started. Post back if you need more. I
chose A1 & A2 as the destination cells in the sheet that changed. I also
wrote the code to exclude any change to a sheet named "Master". Note that
this code will insert the date and time and user name into A1 & A2 whenever
ANY cell in the sheet changes. You might want to limit this to only some
cells or some column(s) or some row(s). Post back if you need to limit the
range.
Note that this macro is a Workbook Event macro and, as such, it MUST be
placed in the Workbook module. To access that module, right-click on the
Excel icon that is found immediately to the left of the word "File" in the
menu that runs across the top of the screen, then select View Code. Note
that this instruction does not apply to Excel 2007.
I don't know what you meant about the "special pricing message". As
written, this macro will display a message box giving the sheet and cell
address whenever a change is made. HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Sh.Range("A1").Value = Now
Sh.Range("A2").Value = Environ("username")
Application.EnableEvents = True
MsgBox "Special Pricing in sheet " & Sh.Name & ", cell " &
Target.Address(0, 0)
End Sub

"Pam M" wrote in message
...
I am looking for a simple way to track when pricing on a worksheet has been
updated. I would like to have one cell contain the update date and if
possible, the username. Any cell on the worksheet may be changed and all
I
want is the cell updated on the worksheet if something on it changes.
Then
that date would remain until something else on the worksheet changes.
Formula or macro doesn't matter, but would prefer one that keeps file size
smaller. I will have several worksheets in a workbook, and each sheet
will
have its own date.

Separately, I would also like to have the master worksheet in the file
show
a "special pricing" message if the pricing from another worksheet is
changed
after the file has been opened.

thanks, Pam

tracking updates
 

Otto--thank you so much. Yes, I do need to limit the range for tracking to
A10:G334. As far as the special pricing comment, this was a separate
thought. I can hold on that for now and repost if I can get this tracking
piece working. Thanks so much for your help.

"Otto Moehrbach" wrote:

Pam
Here is something to get this started. Post back if you need more. I
chose A1 & A2 as the destination cells in the sheet that changed. I also
wrote the code to exclude any change to a sheet named "Master". Note that
this code will insert the date and time and user name into A1 & A2 whenever
ANY cell in the sheet changes. You might want to limit this to only some
cells or some column(s) or some row(s). Post back if you need to limit the
range.
Note that this macro is a Workbook Event macro and, as such, it MUST be
placed in the Workbook module. To access that module, right-click on the
Excel icon that is found immediately to the left of the word "File" in the
menu that runs across the top of the screen, then select View Code. Note
that this instruction does not apply to Excel 2007.
I don't know what you meant about the "special pricing message". As
written, this macro will display a message box giving the sheet and cell
address whenever a change is made. HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Sh.Range("A1").Value = Now
Sh.Range("A2").Value = Environ("username")
Application.EnableEvents = True
MsgBox "Special Pricing in sheet " & Sh.Name & ", cell " &
Target.Address(0, 0)
End Sub

"Pam M" wrote in message
...
I am looking for a simple way to track when pricing on a worksheet has been
updated. I would like to have one cell contain the update date and if
possible, the username. Any cell on the worksheet may be changed and all
I
want is the cell updated on the worksheet if something on it changes.
Then
that date would remain until something else on the worksheet changes.
Formula or macro doesn't matter, but would prefer one that keeps file size
smaller. I will have several worksheets in a workbook, and each sheet
will
have its own date.

Separately, I would also like to have the master worksheet in the file
show
a "special pricing" message if the pricing from another worksheet is
changed
after the file has been opened.

thanks, Pam

tracking updates
 

Pam
Here it is with the range limitation you want. HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range("A10:G334")) Is Nothing Then
Application.EnableEvents = False
Sh.Range("A1").Value = Now
Sh.Range("A2").Value = Environ("username")
Application.EnableEvents = True
'MsgBox "Special Pricing in sheet " & Sh.Name & ", cell " &
Target.Address(0, 0)
End If
End Sub
"Pam M" wrote in message
...
Otto--thank you so much. Yes, I do need to limit the range for tracking
to
A10:G334. As far as the special pricing comment, this was a separate
thought. I can hold on that for now and repost if I can get this tracking
piece working. Thanks so much for your help.

"Otto Moehrbach" wrote:

Pam
Here is something to get this started. Post back if you need more.
I
chose A1 & A2 as the destination cells in the sheet that changed. I also
wrote the code to exclude any change to a sheet named "Master". Note
that
this code will insert the date and time and user name into A1 & A2
whenever
ANY cell in the sheet changes. You might want to limit this to only some
cells or some column(s) or some row(s). Post back if you need to limit
the
range.
Note that this macro is a Workbook Event macro and, as such, it MUST be
placed in the Workbook module. To access that module, right-click on the
Excel icon that is found immediately to the left of the word "File" in
the
menu that runs across the top of the screen, then select View Code. Note
that this instruction does not apply to Excel 2007.
I don't know what you meant about the "special pricing message". As
written, this macro will display a message box giving the sheet and cell
address whenever a change is made. HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
Sh.Range("A1").Value = Now
Sh.Range("A2").Value = Environ("username")
Application.EnableEvents = True
MsgBox "Special Pricing in sheet " & Sh.Name & ", cell " &
Target.Address(0, 0)
End Sub

"Pam M" wrote in message
...
I am looking for a simple way to track when pricing on a worksheet has
been
updated. I would like to have one cell contain the update date and if
possible, the username. Any cell on the worksheet may be changed and
all
I
want is the cell updated on the worksheet if something on it changes.
Then
that date would remain until something else on the worksheet changes.
Formula or macro doesn't matter, but would prefer one that keeps file
size
smaller. I will have several worksheets in a workbook, and each sheet
will
have its own date.

Separately, I would also like to have the master worksheet in the file
show
a "special pricing" message if the pricing from another worksheet is
changed
after the file has been opened.

thanks, Pam

tracking updates
 

This is fabulous. My sincere thanks, Pam

"Otto Moehrbach" wrote:

Pam
Here it is with the range limitation you want. HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range("A10:G334")) Is Nothing Then
Application.EnableEvents = False
Sh.Range("A1").Value = Now
Sh.Range("A2").Value = Environ("username")
Application.EnableEvents = True
'MsgBox "Special Pricing in sheet " & Sh.Name & ", cell " &
Target.Address(0, 0)
End If
End Sub
"Pam M" wrote in message
...
Otto--thank you so much. Yes, I do need to limit the range for tracking
to
A10:G334. As far as the special pricing comment, this was a separate
thought. I can hold on that for now and repost if I can get this tracking
piece working. Thanks so much for your help.

"Otto Moehrbach" wrote:

Pam
Here is something to get this started. Post back if you need more.
I
chose A1 & A2 as the destination cells in the sheet that changed. I also
wrote the code to exclude any change to a sheet named "Master". Note
that
this code will insert the date and time and user name into A1 & A2
whenever
ANY cell in the sheet changes. You might want to limit this to only some
cells or some column(s) or some row(s). Post back if you need to limit
the
range.
Note that this macro is a Workbook Event macro and, as such, it MUST be
placed in the Workbook module. To access that module, right-click on the
Excel icon that is found immediately to the left of the word "File" in
the
menu that runs across the top of the screen, then select View Code. Note
that this instruction does not apply to Excel 2007.
I don't know what you meant about the "special pricing message". As
written, this macro will display a message box giving the sheet and cell
address whenever a change is made. HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
Sh.Range("A1").Value = Now
Sh.Range("A2").Value = Environ("username")
Application.EnableEvents = True
MsgBox "Special Pricing in sheet " & Sh.Name & ", cell " &
Target.Address(0, 0)
End Sub

"Pam M" wrote in message
...
I am looking for a simple way to track when pricing on a worksheet has
been
updated. I would like to have one cell contain the update date and if
possible, the username. Any cell on the worksheet may be changed and
all
I
want is the cell updated on the worksheet if something on it changes.
Then
that date would remain until something else on the worksheet changes.
Formula or macro doesn't matter, but would prefer one that keeps file
size
smaller. I will have several worksheets in a workbook, and each sheet
will
have its own date.

Separately, I would also like to have the master worksheet in the file
show
a "special pricing" message if the pricing from another worksheet is
changed
after the file has been opened.

thanks, Pam

tracking updates
 

Otto
I am getting a compile error on the first line of the macro. Also, I would
like this macro to run on only 7 of the 12 sheets within the workbook.
Thanks, Pam
"Otto Moehrbach" wrote:

Pam
Here it is with the range limitation you want. HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range("A10:G334")) Is Nothing Then
Application.EnableEvents = False
Sh.Range("A1").Value = Now
Sh.Range("A2").Value = Environ("username")
Application.EnableEvents = True
'MsgBox "Special Pricing in sheet " & Sh.Name & ", cell " &
Target.Address(0, 0)
End If
End Sub
"Pam M" wrote in message
...
Otto--thank you so much. Yes, I do need to limit the range for tracking
to
A10:G334. As far as the special pricing comment, this was a separate
thought. I can hold on that for now and repost if I can get this tracking
piece working. Thanks so much for your help.

"Otto Moehrbach" wrote:

Pam
Here is something to get this started. Post back if you need more.
I
chose A1 & A2 as the destination cells in the sheet that changed. I also
wrote the code to exclude any change to a sheet named "Master". Note
that
this code will insert the date and time and user name into A1 & A2
whenever
ANY cell in the sheet changes. You might want to limit this to only some
cells or some column(s) or some row(s). Post back if you need to limit
the
range.
Note that this macro is a Workbook Event macro and, as such, it MUST be
placed in the Workbook module. To access that module, right-click on the
Excel icon that is found immediately to the left of the word "File" in
the
menu that runs across the top of the screen, then select View Code. Note
that this instruction does not apply to Excel 2007.
I don't know what you meant about the "special pricing message". As
written, this macro will display a message box giving the sheet and cell
address whenever a change is made. HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
Sh.Range("A1").Value = Now
Sh.Range("A2").Value = Environ("username")
Application.EnableEvents = True
MsgBox "Special Pricing in sheet " & Sh.Name & ", cell " &
Target.Address(0, 0)
End Sub

"Pam M" wrote in message
...
I am looking for a simple way to track when pricing on a worksheet has
been
updated. I would like to have one cell contain the update date and if
possible, the username. Any cell on the worksheet may be changed and
all
I
want is the cell updated on the worksheet if something on it changes.
Then
that date would remain until something else on the worksheet changes.
Formula or macro doesn't matter, but would prefer one that keeps file
size
smaller. I will have several worksheets in a workbook, and each sheet
will
have its own date.

Separately, I would also like to have the master worksheet in the file
show
a "special pricing" message if the pricing from another worksheet is
changed
after the file has been opened.

thanks, Pam