Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updates | Excel Discussion (Misc queries) | |||
No updates | Excel Discussion (Misc queries) | |||
How to insert tracking numbers into my webpage for RMA tracking | Excel Discussion (Misc queries) | |||
Updates | Setting up and Configuration of Excel | |||
Updates | Excel Discussion (Misc queries) |