Auto Complete with VBA in excel?

[Kaos2800]

Ok, I have to enter in hundreds of phone numbers and the time they called. To make things go quicker I would like to enter something like this.

8.01.0 Which would convert to 8:01 AM
Equally 8.01.1 would amount to 8:01 PM.
The code for this is simple enough. However, is there a way to get VBA to check a cell automatically as you leave it? Or by pressing the down key?

If I had to use a combination like Ctrl-Shift-R to start the program, and then have it autocomplete in the background as I enter stuff would be fine with me.

-David

 

[SkipVought]

Hi,
You can use the Worksheet_Change Event.

1. In the VBE, in the Project explorer, double-click the sheet that you wnat this event to happen. This will activate the code window for that sheet.

2. In the Object drop-down box at the top-left of the window, select Worksheet.

3. In the Procrdure drop-down box at the top-right of the window, select Worksheet_Change.

4. Insert this code to replace every point with a colon...

With Target
       sTime = ""
    For i = 1 To Len(.Value)
        If Mid(.Value, i, 1) = ":" Then Exit Sub
        If Mid(.Value, i, 1) = "." Then
            sTime = sTime & ":"
        Else
            sTime = sTime & Mid(.Value, i, 1)
        End If
    Next
    .Value = sTime
End With

If your column is formatted for time, it should work. Skip,
metzgsk@voughtaircraft.com

 

[Kaos2800]

Thanks!