Ask Mr. Excel - Use event handlers to simplify time input

Back

About MrExcel

Consulting Services

Learn Excel Resources

Challenge of the Month

Bookmark on del.icio.us!

Past Tip of the Week

This week's question comes from John stationed in Okinawa. I am building a spreadsheet to reflect departures and arrivals. There will be basically three cells: Actual Time of Departure, EstimatedTime Enroute, and Estimated Time of Arrival. I would like for the user 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.

To make this work, you need to use an event handler. Event handlers were new in Excel 97 and were discussed back in Tip 006. However, back in tip 006, 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)
End Sub
Enter the following lines of code:
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.

MrExcel.com Consulting can be hired to implement this concept with your data.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a Registered Trademark of Tickling Keys, Inc.