Case Study

You're entering arrival and departure times and want the times to be formatted with a 24-hour clock (Military Time). You've tried formatting the cell, but no matter how you enter the times, they are displayed in the 0:00 hours and minutes format.

The only way to get the time to appear correctly, such as with 23:45, is to have it entered in the cell as such. But typing the colon is time-consuming—it would be much more efficient to simply enter the numbers and let Excel format it for you.

The solution? Use a Change event to take what is in the cell and insert the colon for you:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ThisColumn As Integer

Dim Userlnput As String, NewInput As String

ThisColumn = Target.Column

If ThisColumn < 3 Then

Userlnput = Target.Value If Userlnput > 1 Then

NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & _ Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End If End Sub

An entry of 2345 will display as 23:45. Note that the format change is limited to Columns A and B (If ThisColumn < 3)—without this,entering numbers anywhere on a sheet,such as in a totals column,would force it to be reformatted.

0 0

Post a comment