automatic log

[Miloushka]

hi All,

I'm looking to have excel record in additional columns of my sheet the date, time and user name each time the row is changed.

Basically, i will write comments in column S, which i might have to update sometimes.

I would like columns T and U to record what date i've changed the comment the latest, and who did record the comment (I wont be the only one using this sheet).

The sheet itself is taken from an outside system, and updated every day (a new sheet is made every day, which takes back the comments of the previous one via vlookup, and the older sheets are hidden).

Thanks for your help!

 

[MrKowz]

Right click on the sheet you want this to run on, and click "View Code". Copy and paste the following code into the VBA editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("S:S")) Is Nothing Then
    Target.Offset(0, 1).Value = Now
    Target.Offset(0, 2).Value = Application.UserName
End If
End Sub

 

[rallcorn]

This might be a starting point for you to revise to meet your specific needs. Note that

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long
[COLOR=seagreen]'"Log" is case sensitive - won't work is the actual
'worksheet is all caps, etc[/COLOR].
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
With Sheets("Log")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LR + 1).Value = Now
    .Range("B" & LR + 1).Value = Environ("username")
    .Range("C" & LR + 1).Value = Sh.Name
    .Range("D" & LR + 1).Value = Target.Address(False, False)
    .Range("E" & LR + 1).Value = Target.Value
End With
Application.EnableEvents = True
End Sub

 

[Miloushka]

PS : i had, with help, done the same in another sheet already - but i cant get it to work on this one. I'm thinking maybe this bit of coding refers to stuff already set in other bits of code?

That was the code on the other sheet (that's only part of the code written for the other sheet):

Sub SetLogging(wshtB1 As Worksheet, wshtB2 As Worksheet)
Application.StatusBar = "Updating log"
Dim myRange As Range
Dim myRange2 As Range
Dim i2 As Integer, i1 As Integer, ia As Integer, ib As Integer, cSame As Integer
Dim rSame As Integer, rCount As Integer, z As Integer
Dim strUserName As String

strUserName = GetUserNameWindows

Set myRange = wshtB1.UsedRange
Set myRange2 = wshtB2.UsedRange

With myRange
r1 = .Rows.Count
c1 = .Columns.Count
End With

With myRange2
r2 = .Rows.Count
c2 = .Columns.Count
End With

wshtB1.Activate
rCount = 0
For i1 = 2 To r1
rSame = 0
For i2 = 1 To r2 For ia = 1 To c1 strCellType1 = CStr(wshtB1.Cells(i1, ia).Value)
strCellType2 = CStr(wshtB2.Cells(i2, ia).Value)
If strCellType1 = strCellType2 Then
cSame = 1
Else
cSame = 0
rSame = 0
Exit For End If
Next ia
If cSame = 1 Then
rSame = 1
Exit For
End If
Next i2
If rSame = 0 Then Application.StatusBar = "Updating Log"
rCount = rCount + 1
With wshtB1
.Cells(i1, 12).Value = Now
.Cells(i1, 13).Value = strUserName
End With
End If
Next i1
Application.StatusBar = False

End Sub
Function GetUserNameWindows() As String
GetUserNameWindows = Environ("USERNAME")
End Function

 

[Miloushka]

Right click on the sheet you want this to run on, and click "View Code". Copy and paste the following code into the VBA editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("S:S")) Is Nothing Then
    Target.Offset(0, 1).Value = Now
    Target.Offset(0, 2).Value = Application.UserName
End If
End Sub

Thanks, looks a whole lot easier than the whole thing i had on my previous workbook...
however, it doesnt work either... would you know why maybe?

I would suppose because you said to put it on a sheet - but i'd like to have it on the workbook so that when the workbook is updated (therefore new sheet created), the vlookup takes the comments and the log to the new sheet which would be updated by the log when the comment is changed.

thanks a lot Mr Kowz

 

[MrKowz]

Thanks, looks a whole lot easier than the whole thing i had on my previous workbook...
however, it doesnt work either... would you know why maybe?

I would suppose because you said to put it on a sheet - but i'd like to have it on the workbook so that when the workbook is updated (therefore new sheet created), the vlookup takes the comments and the log to the new sheet which would be updated by the log when the comment is changed.

thanks a lot Mr Kowz

I'm not sure I follow. Where are you going to be entering/updating comments? The log? The new sheet?

Also, What VLOOKUP formula are you needing to use, and what range should it be placed in?

I need a lot more information in order to be able to create working code.

As for your original post, how does that tie in with what is currently being requested?