Solved: User Id And Time Of Data Entry

[Nurofen]

Hi

I have a userform form for data entry, I was wondering is there some way of recording the user Id and the time they entered the data.


Thank you for your time


Nurofen

[Bob Phillips]

Msgbox Environ("Username")
Msgbox Format(Now,"dd mmm yyy hh:mm:ss")

[Nurofen]

Hi Xld,


Thank you,

How do I get it to record to the same line as data entered?(if you can give me tip i'll try to work it out myself if not i'll post back)


Thank you for your time


Nurofen

[Nurofen]

Hi,

I've tried putting the code into the txt box so it will show up and be entered on the same line but thats not working



Private Sub TextBox1_Change()
Environ ("Username")
End Sub


Thank you for your time

Nurofen

[Bob Phillips]

Same line as what?

[Nurofen]

same line as the data entred via the userform

[Nurofen]

hi,

I've tried code I found elsewhere but thats not work correctly either its in This WorkBook. If the details can be entred in columns K and L that would be great

Thank you for your time


Nurofen

[Bob Phillips]

It errors on opening



Private Sub Workbook_Open()
Dim r As Long, OutSht As Worksheet
Set OutSht = Sheets("LOG") 'name of sheet to log changes
r = OutSht.Cells(Rows.Count, 1).End(xlUp).Row + 1
OutSht.Cells(r, 1) = Target.Address 'Cell being altered
OutSht.Cells(r, 2) = Now 'Time cell was altered
OutSht.Cells(r, 3) = Environ("UserName") 'username of person making change
OutSht.Cells(r, 4) = Target.Value
OutSht.Cells(r, 5) = Environ("ClientName")
OutSht.Cells(r, 6) = Environ("ComputerName")
OutSht.Columns("A:F").Columns.AutoFit
End Sub


Target is unknown at this point.

[Nurofen]

Private Sub Workbook_Open()
Dim r As Long, OutSht As Worksheet
Set OutSht = Sheets("LOG") 'name of sheet to log changes
r = OutSht.Cells(Rows.Count, 1).End(xlUp).Row + 1
'OutSht.Cells(r, 1) = CELL CHANGED
OutSht.Cells(r, 2) = Now 'Time cell was altered
OutSht.Cells(r, 3) = Environ("UserName") 'username of person making change
'OutSht.Cells(r, 4) = NEW VALUE
OutSht.Cells(r, 5) = Environ("ClientName")
OutSht.Cells(r, 6) = Environ("ComputerName")
'OutSht.Cells(r, 7) = OLD VALUE
OutSht.Columns("A:F").Columns.AutoFit
End Sub


Ok this seems to work but it doesn't show a few details any chance of guidence to get them working

old value
new value
cell changed

Thank you for your time

Nurofen

[Nurofen]

Kk no problem just found this over at OZgrid. I changed it so it shows me the username name as well.


Thank you for helping me Xld


Nurofen




Dim vOldVal
---------------------------------------------------------

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bBold As Boolean

If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
bBold = Target.HasFormula
With Sheet3
.Unprotect Password:="Secret"
If .Range("A1") = vbNullString Then
.Range("A1:F1") = Array("CELL CHANGED", "OLD VALUE", _
"NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE", "USERNAME")
End If


With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
.Value = Target.Address
.Offset(0, 1) = vOldVal
With .Offset(0, 2)
If bBold = True Then
.ClearComments
.AddComment.Text Text:= _
"OzGrid.com:" & Chr(10) & "" & Chr(10) & _
"Bold values are the results of formulas"
End If
.Value = Target
.Font.Bold = bBold
End With
Environ ("Username")
.Offset(0, 3) = Time
.Offset(0, 4) = Date
.Offset(0, 5) = Environ("UserName")
End With
.Cells.Columns.AutoFit
.Protect Password:="Secret"
End With
vOldVal = vbNullString

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

On Error GoTo 0

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
vOldVal = Target
End Sub

[lucas]

I hope you're not cross-posting without providing a link...if so and you don't understand what the problem is then please read this (http://www.excelguru.ca/node/7).

[Nurofen]

Lucas sorry for the late reply,

No cross post from me, I learnt my leason about that when I first started using fourms.


Nurofen