I am attempting to set up an input mask for the active cell such that the
time can be entered as 3 or 4 digits without the full colon.(815 for 08:15
AM)

In the sample code below, I never get to the numberformat property and the
code sems to be recursively called from itself. I've tried both the
Calculate and the Change events. What's my best solution?
Thanks!
Lee

Private Sub Worksheet_Some_event(ByVal Target As Range
Dim InTime As String
Dim Apostrophe As String
Apostrophe = ""
If Len(Target) = 4 Then ' Allow 815 instead of 0815
InTime = Apostrophe & Left(Target.Value, 2) & ":" & Right(Target.Value, 2) &
Apostrophe
Else: InTime = Apostrophe & "0" & Left(Target.Value, 1) & ":" &
Right(Target.Value, 2) & Apostrophe
End If
ActiveCell.Value = InTime
ActiveCell.NumberFormat = "hh:mm A"
End Sub

First, you can't make up your own events. But you can tie into the
worksheet_Change event.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C:C")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(Target.Value) = False Then Exit Sub
If Int(Target.Value) < Target.Value Then Exit Sub
If Target.Value < 0 Then Exit Sub

If Target.Value < 9999 Then
Application.EnableEvents = False
Target.Value = Format(Target.Value, "00:00")
Target.NumberFormat = "hh:mm AM/PM"
End If

errHandler:
Application.EnableEvents = True

End Sub

Notice the application.enableevents stuff. That's what stops the change to the
worksheet from calling the worksheet_change event.

You may want to take a look at how Chip Pearson approached the subject:
http://cpearson.com/excel/DateTimeEntry.htm






Lee Hunter wrote:

I am attempting to set up an input mask for the active cell such that the
time can be entered as 3 or 4 digits without the full colon.(815 for 08:15
AM)

In the sample code below, I never get to the numberformat property and the
code sems to be recursively called from itself. I've tried both the
Calculate and the Change events. What's my best solution?
Thanks!
Lee

Private Sub Worksheet_Some_event(ByVal Target As Range
Dim InTime As String
Dim Apostrophe As String
Apostrophe = ""
If Len(Target) = 4 Then ' Allow 815 instead of 0815
InTime = Apostrophe & Left(Target.Value, 2) & ":" & Right(Target.Value, 2) &
Apostrophe
Else: InTime = Apostrophe & "0" & Left(Target.Value, 1) & ":" &
Right(Target.Value, 2) & Apostrophe
End If
ActiveCell.Value = InTime
ActiveCell.NumberFormat = "hh:mm A"
End Sub

--

Dave Peterson

Dave,

How absolutely simple and elegant! Thanks for the lesson. Much appreciated

Lee

"Dave Peterson" wrote:

First, you can't make up your own events. But you can tie into the
worksheet_Change event.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C:C")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(Target.Value) = False Then Exit Sub
If Int(Target.Value) < Target.Value Then Exit Sub
If Target.Value < 0 Then Exit Sub

If Target.Value < 9999 Then
Application.EnableEvents = False
Target.Value = Format(Target.Value, "00:00")
Target.NumberFormat = "hh:mm AM/PM"
End If

errHandler:
Application.EnableEvents = True

End Sub

Notice the application.enableevents stuff. That's what stops the change to the
worksheet from calling the worksheet_change event.

You may want to take a look at how Chip Pearson approached the subject:
http://cpearson.com/excel/DateTimeEntry.htm






Lee Hunter wrote:

I am attempting to set up an input mask for the active cell such that the
time can be entered as 3 or 4 digits without the full colon.(815 for 08:15
AM)

In the sample code below, I never get to the numberformat property and the
code sems to be recursively called from itself. I've tried both the
Calculate and the Change events. What's my best solution?
Thanks!
Lee

Private Sub Worksheet_Some_event(ByVal Target As Range
Dim InTime As String
Dim Apostrophe As String
Apostrophe = ""
If Len(Target) = 4 Then ' Allow 815 instead of 0815
InTime = Apostrophe & Left(Target.Value, 2) & ":" & Right(Target.Value, 2) &
Apostrophe
Else: InTime = Apostrophe & "0" & Left(Target.Value, 1) & ":" &
Right(Target.Value, 2) & Apostrophe
End If
ActiveCell.Value = InTime
ActiveCell.NumberFormat = "hh:mm A"
End Sub

--

Dave Peterson