Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - desparate for an expanded history log without sharing a workbook
I received a reply to my question about Binzelli and it was quite
helpful. Unfortunately, it was limited to changes in cells - not groups of cells, inserting columns, etc. Is there anybody out there that can expand on Binzelli procedure? I changed the log to include "formula" instead of "value" and I added who made the change using document properties and also added the time that the change was made. Unfortunately, I left this at work so I don't have an example. But, as you can see, the code does what I asked - I just didn't ask for enough. Any help would be much appreciated, because, as I said before, I'm on a really tight deadline and I'm very concerned that I won't be able to come through with anything worthwhile. Thanks very much in advance, Anita Anita, No problem, use the Worksheet_Change event in the code for the sheet you want to "track" (right click on the sheet tab and select the menu item to add code to that sheet) The code below writes the change to a sheet called "Log" The row used to write the last change is stored in cell B1 and is incremented each time the event fires. Private Sub Worksheet_Change(ByVal Target As Range) Dim Lastrow As Long Lastrow = ActiveWorkbook.Sheets("Log").Range("B1").Value 'Write the Change ActiveWorkbook.Sheets("Log").Cells(Lastrow + 1, 1) = "Sheet (name) - Row " & Target.Row & " - Column " & Target.Column & " changed to: " & Target.Value 'Increase the LastRow value by 1 ActiveWorkbook.Sheets("Log").Range("B1").Value = Lastrow + 1 End Sub Good luck "a" wrote in message link.net... Hi, Is there a way to track changes made to a worksheet using VBA? I was thinking that perhaps there was an event procedure. I envision that the change could be displayed on a hidden sheet when a change is made - similar to the tracking changes tool but without sharing a workbook. I'm on a deadline with this one and so any help would be greatly appreciated. I've asked about this issue before and haven't received any replies and so I'm thinking that maybe this cannot be done. Thanks in advance for any help, anita |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - desparate for an expanded history log without sharing a workbook
Anita,
Check my answer to a followup of your post: Your first 'problem' is solved (in the code below) by checking if the target range has more than one column or row (If Target.Columns.Count 1 Or Target.Rows.Count 1 Then). If that is the case the code then loops through each column and row and retrieves the value of the cells(col,row). The sample code shows the change in a messagebox, you will have change the code to put it in the "log sheet". As for the second problem, the property "HasFormula" detects if the particular cell in the target range contains a formula or a value. In the first case the messagebox returns the ".Formula" property, else the ".Value". As for the third problem. The "Worksheet_Change" event is only triggered when something is actually entered into a cell. Not when a cell's value changes because it contains a formula dependent on the changed cell. You can check that with the code below . For example put value 1 in A1, 2 in A2 and formula "=SUM(A1:A2)" in A3. All three entries trigger the Worheets_Change event and show what has changed. When you now change A1 to another value, the event only shows that cell A1 was changed, not the value in A3. The code below, should do the trick: Private Sub Worksheet_Change(ByVal Target As Range) Dim Col As Long Dim Row As Long If Target.Columns.Count 1 Or Target.Rows.Count 1 Then For Col = Target.Column To Target.Column + Target.Columns.Count - 1 For Row = Target.Row To Target.Row + Target.Rows.Count - 1 If Cells(Row, Col).HasFormula Then MsgBox ("Row " & Row & " in column " & Col & " has changed to formula: " & Cells(Row, Col).Formula) Else MsgBox ("Row " & Row & " in column " & Col & " has changed to value: " & Cells(Row, Col).Value) End If Next Row Next Col Else If Target.HasFormula Then MsgBox ("Row " & Target.Row & " in column " & Target.Column & " has changed to formula: " & Target.Formula) Else MsgBox ("Row " & Target.Row & " in column " & Target.Column & " has changed to value: " & Target.Value) End If End If End Sub Good luck "a" wrote in message link.net... I received a reply to my question about Binzelli and it was quite helpful. Unfortunately, it was limited to changes in cells - not groups of cells, inserting columns, etc. Is there anybody out there that can expand on Binzelli procedure? I changed the log to include "formula" instead of "value" and I added who made the change using document properties and also added the time that the change was made. Unfortunately, I left this at work so I don't have an example. But, as you can see, the code does what I asked - I just didn't ask for enough. Any help would be much appreciated, because, as I said before, I'm on a really tight deadline and I'm very concerned that I won't be able to come through with anything worthwhile. Thanks very much in advance, Anita Anita, No problem, use the Worksheet_Change event in the code for the sheet you want to "track" (right click on the sheet tab and select the menu item to add code to that sheet) The code below writes the change to a sheet called "Log" The row used to write the last change is stored in cell B1 and is incremented each time the event fires. Private Sub Worksheet_Change(ByVal Target As Range) Dim Lastrow As Long Lastrow = ActiveWorkbook.Sheets("Log").Range("B1").Value 'Write the Change ActiveWorkbook.Sheets("Log").Cells(Lastrow + 1, 1) = "Sheet (name) - Row " & Target.Row & " - Column " & Target.Column & " changed to: " & Target.Value 'Increase the LastRow value by 1 ActiveWorkbook.Sheets("Log").Range("B1").Value = Lastrow + 1 End Sub Good luck "a" wrote in message link.net... Hi, Is there a way to track changes made to a worksheet using VBA? I was thinking that perhaps there was an event procedure. I envision that the change could be displayed on a hidden sheet when a change is made - similar to the tracking changes tool but without sharing a workbook. I'm on a deadline with this one and so any help would be greatly appreciated. I've asked about this issue before and haven't received any replies and so I'm thinking that maybe this cannot be done. Thanks in advance for any help, anita |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Desparate For Help - Pivot Table Field List | Excel Discussion (Misc queries) | |||
Workbook cannot be expanded to fill screen | Excel Discussion (Misc queries) | |||
Desparate..please help | Excel Worksheet Functions | |||
cant not change history on a shared workbook | Excel Discussion (Misc queries) | |||
Problem with shared workbook (history sheet) | Excel Worksheet Functions |