Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
to report change in wrksheet in another sheet, not usin track chan
Hi all,
This is a sort of a follow up question for one posted on 22nd May 2006. The original question and it's two most important replies are as follows: Original: "Ukyankee" a écrit dans le message de news: ... I have a spreadsheet used to track actions by numerous folks. There is a column for a due date that I would like to see when and how often it is changed. I looked at Track Changes, but it tracks ALL changes in all cells (adds, edits etc) - which is too much info. I would like to see only the changes on a particular column after the initial entry. Any thoughts would be greatly appreciated. ' Reply 1: 'Here is one solution: http://cjoint.com/?fwk4yzHeiZ You'll have to change the constants in the event proc to suit your needs. Following code should be placed in worksheet's code: HTH -- AP '------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) 'Column to be watched Const sWatch As String = "F" 'Column of reference data that will show on Track sheet Const sRef As String = "A" Dim rWatch As Range Dim rCell As Range Dim sUser As String Dim lOffset As Long Set rWatch = Intersect(Target, Columns(sWatch)) If rWatch Is Nothing Then Exit Sub sUser = Environ("username") lOffset = Columns(sRef).Column - Columns(sWatch).Column With Worksheets("Track") For Each rCell In rWatch With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) .Value = rCell.Offset(0, lOffset) .Offset(0, 1).Value = Now .Offset(0, 2).Value = sUser .Offset(0, 3).Value = rCell.Value End With Next rCell End With '------------------------------ End Sub Reply 2 which is explaining what the above code does in the user's spreadsheet: "Ken Johnson" wrote: Hi Ukyankee, Ardus Petus posted the response, however... to get the code in place... 1. Copy it, starting at "Private Sub Worksheet_Change(ByVal Target As Range)" and finishing at "End Sub" (inclusive) 2. In your workbook right click the sheet tab of the sheet with the data column you are wanting tracked. 3. Select "View Code" from the popup menu. This takes you to the sheet's code module. 4. Paste the code into the code module (white space) 5. The code refers to the column being tracked - Const sWatch As String = "F" Change the "F" to the column you are wanting tracked. 6. The code refers to a reference column - Const sRef As String = "A" Change the "A" to the column you are wanting to appear associated with the tracked column 7. Press Alt + F11 to return to the sheet 8. The code refers to a sheet named "Track" so you will have to name a spare sheet in your workbook "Track" for the code to work. The code is an example of an Event Procedure, macros that are automatically triggered by certain changes that occur during the operation of Excel. This Event procedure is triggered every time the user changes a cell's value on the sheet with the code in its code module. Do a Google search eg "VBA Books" to find some titles. I started out with John Walkenback's "Excel 97 Programming for Dummies" and John Green's "Excel 2000 VBA Programmer's Reference" (Wrox Press) Ken Johnson ---------------------------------------------------------------------------------------- Now, I have a similar query, only that instead of one column there are a few more. Its a spreadsheet with say products and their ID's and a final column with no. of customers who have purchased that product. now, the number of customers for each product could change, but also there could be new products added to this sheet. At the end of each month I would like to see a separate sheet (like the sheet named 'Track' in the above code) which highlights changes in the 'customers' tab, and also in the product tab (to highlight the new products entered in that month). If I could have changes in say the 'product' and 'ID' columns in one sheet and the changes in 'customer' column in another sheet it would solve another issue although it's not very important. I was thinking that adding another column being tracked along side 'F' would work but since I don't know VBA, it's probably not the solution (it gave an error on compiling ...' Column to be watched Const sWatch As String = "F", "G" '...the "G" was my addition to the code) Also I don't think track changes can produce a column sppecific report like the above code does. if it can please let me know. Anyways, please help me if you can... desperately need this one Thanks a lot in advance, Epoch |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I keep a "running total" from wrksheet to wrksheet? | Excel Worksheet Functions | |||
Use date modified to change format & create filter to track change | Excel Worksheet Functions | |||
ADD MONIES USIN EXCEL SPREAD SHEET | New Users to Excel | |||
sum function usin an IF statement | Excel Discussion (Misc queries) | |||
Can I use conditional formatting between two sheets to track chan. | Excel Discussion (Misc queries) |