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
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
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