tracking updates

G

Guest

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
 
O

Otto Moehrbach

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
 
G

Guest

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

Otto Moehrbach

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
 
G

Guest

This is fabulous. My sincere thanks, Pam

Otto Moehrbach said:
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
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top