Worksheet_Change Event

[MUKESHY12390]

Dim PreviousValue


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value <> PreviousValue Then
          Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
            Application.UserName & " changed cell " & Target.Address _
            & " from " & PreviousValue & " to " & Target.Value
               End If
          End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
End Sub

[/QUOTE]




Hello frnds....

I am getting huge trouble with workbook event, above event working fine .
But when I change more than one cell at time (means together)then event producing error..!!

Is there any other way I can track changes done by the user
more thankful ....In case of any suggestion and help..!!



Thanks in Advance..!!!

 

[Mytå]

Hi

Try this, but I'm doubt

Dim PreviousValue


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells(1, 1) <> PreviousValue Then
        Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
        Application.UserName & " changed cell " & Target.Cells(1, 1).Address _
        & " from " & PreviousValue & " to " & Target.Cells(1, 1).Value
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Cells(1, 1).Value
End Sub

Mytå

 

[Peter_SSs]

Is there any other way I can track changes done by the user
more thankful ....In case of any suggestion and help..!!

My suggestion would be this.

Copy the sheet in question and name that sheet "Record". This sheet will record the "PreviousValue" for all cells. It could subsequently be hidden, as I suspect your "Log" sheet already is.

Try this Worksheet_Change code in the original sheet instead of the code you had.

Test in a copy of your workbook.


<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>  <SPAN style="color:#00007F">Dim</SPAN> Cell <SPAN style="color:#00007F">As</SPAN> Range<br>  <SPAN style="color:#00007F">Dim</SPAN> UN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> PreviousValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>  <br>  UN = Application.UserName<br>  <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> Target<br>    PreviousValue = Sheets("Record").Range(Cell.Address).Value<br>    <SPAN style="color:#00007F">If</SPAN> Cell.Value <> PreviousValue <SPAN style="color:#00007F">Then</SPAN><br>      Sheets("log").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = _<br>            UN & " changed cell " & Cell.Address _<br>            & " from " & PreviousValue & " to " & Cell.Value<br>      Sheets("Record").Range(Cell.Address).Value = Cell.Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> Cell<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Peter_SSs]

Actually, this might be a little neater

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>  <SPAN style="color:#00007F">Dim</SPAN> Cell <SPAN style="color:#00007F">As</SPAN> Range<br>  <SPAN style="color:#00007F">Dim</SPAN> UN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Addr<br>  <SPAN style="color:#00007F">Dim</SPAN> PreviousValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>  <br>  UN = Application.UserName<br>  <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> Target<br>    Addr = Cell.Address(0, 0)<br>    PreviousValue = Sheets("Record").Range(Addr).Value<br>    <SPAN style="color:#00007F">If</SPAN> Cell.Value <> PreviousValue <SPAN style="color:#00007F">Then</SPAN><br>      Sheets("log").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = _<br>            UN & " changed cell " & Addr _<br>            & " from " & PreviousValue & " to " & Cell.Value<br>      Sheets("Record").Range(Addr).Value = Cell.Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> Cell<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[MUKESHY12390]

thanks Myta for quick replay..............and your code working same as mine.! m still in trouble..!!

 

[MUKESHY12390]

thanks ..peter for your quick replay..!!!!

first I wanted to tell you....what I did.. after your suggestion.

I changed Sheets(" sheet1") with sheets("Record") then paste entire Event.

And still Sheets("log") not showing any track..........!!
is that I am missing something
pls help me...........!!

 

[Peter_SSs]

I changed Sheets(" sheet1") with sheets("Record")

No, I didn't suggest that you changed the name of your sheet, I suggested that you copy the whole sheet and name the copy of the sheet "Record".

I'll detail it a little more. I'm assuming that the sheet you want to track is called "Sheet1"

1. Remove any Worksheet code from "Sheet1"'s module.

2. Make a copy of "Sheet1" (One way is to right click the "Sheet1" name tab and choose "Move or Copy", Click "Create a Copy" and "OK"

3. With this new sheet (probably called "Sheet1 (2)"), change its name to "Record"

4. Put the code I posted into the "Sheet1" module.

5. Test

 

[MUKESHY12390]

Thank You So Much.........Peter..!!
Now...It's really working great!!