MrExcel.com - Your One Stop for Excel Tips & Solutions

Enter Time Without Colon

January 22, 2002 - by

This week's Excel question comes from John stationed in Okinawa.

I am building an Excel spreadsheet to reflect departures and arrivals. There will be basically three cells: Actual Time of Departure, Estimated Time Enroute, and Estimated Time of Arrival. I would like for the person to just be able to enter (for instance) 2345 and have the cell automatically format the display to show 23:45. What I'm getting instead is 0:00, regardless of formula or formatting. And, the calculation won't display anything but 0:00 if the user fails to shift key and colon. I know it seems simple to do so, however, every little second saved counts, especially when entering similar data over and over again in Excel.

To make this work, you need to use an event handler. Event handlers were new in Excel 97 and were discussed back in Run a Macro Every Time a Cell Value Changes in Excel. However, back in that tip, the event handler was applying a different format to certain cells. This application is slightly different, so let's revisit the event handler.

An event handler is a small bit of macro code that gets executed every time that a certain event happens. In this case, we want the macro to run whenever you change a cell. To set up an event handler, follow these steps:

  • An event handler is associated with just a single worksheet. Start from that worksheet and hit alt-F11 to open the VB editor.
  • In the upper left window (Project - VBA Project) double click the name of your worksheet.
  • In the right pane, click the left drop down and change general to Worksheet.
  • In the right drop down, pick Change.

This will cause Excel to pre-enter the following macro shell for you: 

Private Sub Worksheet_Change(ByVal Target As Range)
	UserInput = Target.Value
	If UserInput > 1 Then
		NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
		Application.EnableEvents = False
		Target = NewInput
		Application.EnableEvents = True
	End If
End Sub

Any time a cell is changed, the cell that was changed is passed to this program in the variable called "Target". When someone enters a time with a colon in the worksheet, it will evaluate to a number less than one. The If block makes sure to only change cells if they are greater than one. I use the left() and right() functions to break the user input into hours and minutes and insert a colon in between.

Whenever the user enters "2345", the program will change this entry to 23:45.

Possible Enhancements

If you want to limit the program to only work on columns A&B, you can check the value of Target.Column and only execute the code block if you are in the first two columns: 

Private Sub Worksheet_Change(ByVal Target As Range)
    ThisColumn = Target.Column
    If ThisColumn < 3 Then
        UserInput = Target.Value
        If UserInput > 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

If you ever want to make changes to the worksheet without colons being entered (for example is you need to add formulas or change headings, etc.) you can turn of the event handler with this short macro: 

Sub TurnEventHanderOff()
	Application.EnableEvents = False
End Sub
You can turn event handlers back on with this macro:
Sub TurnEventHanderOff()
	Application.EnableEvents = True
End Sub

If you take this concept and change it, there is an important concept to be aware of. When the event handler macro assigns a new value to the cell referenced by Target, Excel counts this as a worksheet change. If you do not briefly turn of event handlers, then Excel will recursively start calling the event handler and you get unexpected results. Before making a change to a worksheet in a change event handler, be sure to temporarily suspend event handling with the Application.EnableEvents line.