Trapping Cell Input BEFORE Cell Updates

**PosseJohn** · 10-31-2011, 03:03 AM

Greetings,

I have a Worksheet that provides a cell for the user to enter a Date/Time.

The cell is formated as Date/Time.

I've had some feedback from users that it would be nice just to enter the time without a semi-colon to obtain the current date + the time they entered.

For instance, if they enter 2115, the cell would show the current date with time of 21:15,
i.e. 10/31/2011 21:15.

My problem is that if they enter 2115, the cell updates and formats the contents to 10/15/1905 00:00, THEN calls the WORKSHEET CHANGE event.

Is there a BEFORE CHANGE event that isn't documented?

Any other solutions?

**foxguy** · 10-31-2011, 03:12 AM

There is no BeforeChange event.
If I was doing this, I would format the cell as "Text" and have Worksheet_Change() put the value back as text that looks like a date.

Sub Worksheet_Change(ByVal Target as Range)
    Dim s As String

    If Not Intersect(Target, Range("Date")) Is Nothing Then
        s = Right("00" & Target.Value, 4)
        s = Left(s, 2) & ":" & Mid(s, 3)
        s = Format(Date, "MM/DD/YY") & " " & s
        Target.Value = s
    End If
End Sub

**PosseJohn** · 10-31-2011, 04:23 AM

Sorry

Here's what I ended up with...

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Target.Address = "$F$3" Then
        Application.EnableEvents = False
        If Not IsDate(Range("F3")) And Val(Range("F3")) < 2400 And IsNumeric(Range("F3")) Then 'User input was just time WITHOUT semi-colon.
            varTime = Right("00" & Target.Value, 4)
            varTime = Left(varTime, 2) & ":" & Mid(varTime, 3)
            varTime = Format(Date, "mm-dd-yy") & " " & varTime
            Target.Value = varTime
        ElseIf Not IsDate(Range("F3")) And (Val(Range("F3")) >= 2400 Or Not IsNumeric(Range("F3"))) Then
            MsgBox "You must provide something that resembles a Time.", vbApplicationModal + vbCritical + vbOKOnly + vbSystemModal, "Invalid Input"
            Range("F3").Select
        ElseIf IsDate(Range("F3")) And Int(DateValue(Range("F3"))) = 0 Then 'User input was just time WITH semi-colon.
            Range("F3") = Format(Int(Now()) + TimeValue(Range("F3").Value), "mm-dd-yy hh:mm")
        End If
        If DateValue(Range("F3")) + TimeValue(Range("F3")) > Now() Then
            Range("F3") = Format(DateValue(Range("F3")) + TimeValue(Range("F3")) - 1, "mm-dd-yy hh:mm")
        End If
        Application.EnableEvents = True
    End If

Thank again for the response.

**foxguy** · 10-31-2011, 07:01 AM

I strongly recommend that you Name $F$3 something. At a later date you may wish to redesign the sheet and move $F$3 to some other place. If the macro is looking for $F$3 then it will crash when the cell is moved. But if the cell is Named "Date", or "RptDate" or anything and the macro is looking for Range("Date") instead of Range("F3") then it will still find it when the cell is moved.