Noting that User has used 'Enter' on a cell and not a Tab

Hi, Thanks to all who read this, regardless if you know the answer!

I'm using the Worksheet_Change to set column D1:D1000 so that any change in that column triggers an event

The event is simply to put in column H the time that this change happend.

All is fine should the User tab across the sheet and enters the values into columns A,B,C,D,E.

However should the User hit the 'Enter' key on column D then the system puts the time entry on the next row, as the enter has caused Excel to move one row down.

Question, how do you either

a) Stop the User using Enter
b) Note that an Enter has been used and not a Tab and therefore use a different event
c) Something else?

My current code is below for reference

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D1:D1000")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim HoldPos As String
Dim RowVal As String
Dim TimeRange As String

HoldPos = ActiveCell.Address
Application.EnableEvents = False
RowVal = ActiveCell.Row
TimeRange = "H" & RowVal
Range(TimeRange).Select
ActiveCell.Value = Time
Range(HoldPos).Select
Application.EnableEvents = True

End Sub

I'm fairly new to this, so apologies if the answer is 'under my nose'.

Thanks
Steve

Nick's picture

HoldPos =

HoldPos = Target.Address

Application.EnableEvents = False

RowVal = Target.Row

 

also take a look here (might be interesting too):

19. VBA Tips - Log An Audit Trail

Sorted

Thanks Nick!

Does the job nicely

Nick's picture

WONDERFUL..Should You wish

wonderful..

Should you wish to say thanks (hint hint), we'd appreciate a supportive click on the links here:

excelexperts.com/Free-VBA-Training-Videos

tks

Nick