MACRO for recording changes Does not work properly

[Coval]

Hi,
i hope that you guys can help...

I have macro that records changes made to a worksheet called: "Product BOM - 070".
Changes however are recorded to following worksheet:

"

Here is the code I hv:

Dim old_value As String
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="abcabc"
If Not Intersect(Target, Range("A7:R" & UsedRange.SpecialCells(xlCellTypeLastCell).row)) Is Nothing Then
Sheets("Revision Table - 070").Unprotect Password:="abcabc"
lr = Sheets("Revision Table - 070").Range("A" & Rows.Count).End(xlUp).row + 1
Sheets("Revision Table - 070").Cells(lr, 1).Value = Cells(Target.row, "a")
Sheets("Revision Table - 070").Cells(lr, 2).Value = Cells(Target.row, "b")
Sheets("Revision Table - 070").Cells(lr, 3).Value = Target.Address
Sheets("Revision Table - 070").Cells(lr, 4).Value = Cells(6, Target.Column).Value & ": " & old_value
Sheets("Revision Table - 070").Cells(lr, 5).Value = Target.Value
Sheets("Revision Table - 070").Cells(lr, 6).Value = Now
Sheets("Revision Table - 070").Cells(lr, 7).Value = Date
Sheets("Revision Table - 070").Cells(lr, 8).Value = Environ("Username")
Sheets("Revision Table - 070").Protect Password:="abcabc"
End If
ActiveSheet.Protect Password:="abcabc"


End Sub


Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count < 2 Then
If Target.Value <> "" Then old_value = Target.Value
End If
End Sub

Any ideas? Will Greatly apprecite.



Coval

 

[Andrew Poulsom]

You can't post an image saved on your computer? What is the problem in words?

 

[Coval]

You can't post an image saved on your computer? What is the problem in words?

Hi Andrew,
Here is the link to Excel file I uploaded to DropBox:

https://www.dropbox.com/s/ivs7vjniclczd1k/file.xlsm?dl=0

Coval

 

[Andrew Poulsom]

And the problem is?

 

[Coval]

And the problem is?

Hey Andrew,
I apologize for not describing it. I am tired after working almost 3 days without sleep.

The problem is that Macro does not populate correctly "Revision table - 070" or does not populate Revision table at all (Focus is on column A & B of the revision table) - as you can see following code is responsible for populating those:

Sheets("Revision Table - 070").Cells(lr, 1).Value = Cells(Target.row, "a")
Sheets("Revision Table - 070").Cells(lr, 2).Value = Cells(Target.row, "b")

Not sure why macro does not want to run. Sometimes it runs but when it does, it is populating only single row in Revision table - 070" disregarding how many changes I make in the BOM worksheet...and it is actually replacing old modification record with new one while it should record old in a certaimn row, and new in the row below...

Hope it makes sense now.

Let me know if there is anything I can make more clear...I hope I can make another shot for explanation...not easy though with so huge amount of working hours behind me.

Coval

 

[Andrew Poulsom]

Do you get an error if you change more than one cell?

 

[Coval]

I did not get to this point of code development. It will probably give me an error as I did not include any code that will manage errors.
Macro is already causing problems when i try to delete rows in the worksheet: "PRODUCT BOM - 070".

However, my knowledge of macros is limited. The macro that I have in a spreadsheet was jointly created by me and friend of mine basing on what I received from a forum user. We just tweaked it and added some lines of code.

 

[Coval]

Any help? I will greatly appreciate.

 

[Andrew Poulsom]

I still don't know what the problem is.