Change cell if wrong time entered
 

Hi Team

Trust you're all well.

I have fat fingers which, when excited tend to enter the time incorrectly whilst using the NumPad ( And No! before anyone asks, I won't be using the qerty numbers as workday is not that long.. :) ).

It's only ever one time extension that makes life miserable, anything with a 00:45, for example, if I want 09:45, I tend to enter it as 09:54.

I tried using a loop but I think the syntax is wrong.

Sub FixTime()

Dim myRange As Range, c As Range
Dim WrongMin As Integer, RightMin As Integer

Set myRange = Sheets("TMS DATA").Range("AB6:AE250")
WrongMin = Minute("00:54:00")
RightMin = Minute("00:45:00")

For Each c In myRange
If Not c = "" Then
With c
If c = WrongMin Then c = RightMin
End With
End If
Next c

End Sub

I was kinda hopeful someone could do two things he

1. get the right syntax
2. change it so that I can insert it behind the sheet and use the SheetChange(ByVal)

I also tried using a couple of examples of the SheetChange but I couldn't get that to work either.

As always

TIA
Cheers
Mark.

Change cell if wrong time entered
 

Hi Mark,

Am Wed, 7 Feb 2018 02:34:41 -0800 (PST) schrieb Living the Dream:

It's only ever one time extension that makes life miserable, anything with a 00:45, for example, if I want 09:45, I tend to enter it as 09:54.

<snipped

1. get the right syntax
2. change it so that I can insert it behind the sheet and use the SheetChange(ByVal)

try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("AB6:AE250")) Is Nothing _
Or Target.Count 1 Then Exit Sub

If Minute(Target) = 54 Then Target = TimeSerial(Hour(Target), 45, 0)
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Change cell if wrong time entered
 

Hi Claus

Always there when I need something, a big thank you.

Many thanks
Mark.

Change cell if wrong time entered
 

On Thursday, February 8, 2018 at 4:29:28 AM UTC-5, Living the Dream wrote:
Hi Claus

Always there when I need something, a big thank you.

Many thanks
Mark.

Claus Rocks.