I submitted a post awhile back that was beautifully solved by 6StringJazzer to log user responses to a data entry validation via VB for Excel.
The post is herehttp://www.excelforum.com/excel-prog...alidation.html
However, I received another request from a colleague of mine that it'd be nice that in the log that was created, it could also log the cell location AND value input. It seems like this would be possible b/c I can see exactly where the line in the code that prints out the current information and I just need it to be expanded to also log where the value was entered (cell location) as well as the value the user enters.
Can anyone assist with this?
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim answer As String
If Target.Count = 1 Then
If Target.Value > Sheet2.Range("threshold") Then
formAsk.Show
answer = formAsk.textboxWhom
If answer <> Sheet2.Range("rightname") Then
MsgBox "Please contact RP ALARA for dose approval."
End If
On Error GoTo BackupFile
Open ThisWorkbook.Path & "\responselog.txt" For Append Access Write Lock Write As #2
On Error GoTo 0
Dim ThisUser As String
'thisuser = Application.username ' registration in MS Office
ThisUser = Environ("USERNAME") ' Windows login
Print #2, Now & " User: " & ThisUser & " Response: " & answer Close 2
End If
End If
Exit Sub
BackupFile:
Open ThisWorkbook.Path & "\responselog2.txt" For Append Access Write Lock Write As #2
Print #2, Now & " User: " & ThisUser & " Response: " & answer
Close 2
End Sub
Thanks
Bookmarks