Help Needed - Keypress??

[InfoH]

Hi all,

I have a spreadsheet whereby anything entered into a cell between B2:B5000 it then would update the relevent row on columns C and D with both Username and Timestamp.

Now that bit works fine, however if they enter something into B2 and press Enter or Down, it will put the username and timestamp into C3 and D3

this is because it uses the activecell to judge where to put the username and timestamp

so my solution to this (which may very well be more complicated than it needs to be) was to do some if statements for the following

if down arrow is pressed then.. select target cell, move to the right, input username, move to the right, input timestamp and then do orignally selected down arrow to put the user on the cell they requested

Please see my code below and advise whats wrong with it

thanks in advance

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B1:B5000")) Is Nothing Then
On Error Resume Next
If KeyAscii = 13 Then
Application.EnableEvents = False
Target.Cells.Select
ActiveCell.Offset(0, 1) = Application.UserName
ActiveCell.Offset(0, 2).Value = Now
Application.EnableEvents = True
ActiveCell.Offset(1, 0).Select
 
ElseIf KeyAscii = 37 Then
Application.EnableEvents = False
Target.Cells.Select
ActiveCell.Offset(0, 1) = Application.UserName
ActiveCell.Offset(0, 2).Value = Now
Application.EnableEvents = True
ActiveCell.Offset(0, -1).Select
 
ElseIf KeyAscii = 38 Then
Application.EnableEvents = False
Target.Cells.Select
ActiveCell.Offset(0, 1) = Application.UserName
ActiveCell.Offset(0, 2).Value = Now
Application.EnableEvents = True
ActiveCell.Offset(-1, 0).Select
 
ElseIf KeyAscii = 39 Then
Application.EnableEvents = False
Target.Cells.Select
ActiveCell.Offset(0, 1) = Application.UserName
ActiveCell.Offset(0, 2).Value = Now
Application.EnableEvents = True
ActiveCell.Offset(0, 1).Select
 
ElseIf KeyAscii = 40 Then
Application.EnableEvents = False
Target.Cells.Select
ActiveCell.Offset(0, 1) = Application.UserName
ActiveCell.Offset(0, 2).Value = Now
Application.EnableEvents = True
ActiveCell.Offset(1, 0).Select
On Error GoTo 0
End If
End If
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

You need to use Target instead of ActiveCell. You can forget all the KeyAscii stuff - that variable will be empty.

 

[InfoH]

lol i knew i was over complicating the matter!

thanks very much

 

[venkat1926]

I am not sure whether I have understood your problem correctly

anyhow try this event code(you know wheres to park it )

and see whether it gets what you want

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Target.Offset(0, 1) = Application.UserName
ElseIf Target.Column = 3 Then
'MsgBox Target.Address
Target.Offset(0, 1) = Now
Else
Exit Sub
End If
End Sub

enter anything in any cell in column B and see what happens in C and D in the same row
if it is ok save the file

 

[RoryA]

Do you want to do anything if they delete the cell contents? (your code doesn't as it stands)