Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change cell if wrong time entered
Hi Claus
Always there when I need something, a big thank you. Many thanks Mark. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to place real time in a cell when data entered in an adjacent cell | Excel Programming | |||
Change value of cell automatically if entered the wrong number | Excel Worksheet Functions | |||
typed numbers entered in cell wrong | Excel Discussion (Misc queries) | |||
date and time entered when a cell contains data | Excel Programming | |||
How to get a cell to error if the wrong figue is entered | Excel Discussion (Misc queries) |