Excel VBA

iszatmary used Ask the Experts™
Hi Guys,
I have an excel sheet where I should introduce dates in the following format:
12:40 or 2:34...
I need to make a Macro in that way that when a user are typing in 1240 or 234 the ":" character to be placed in its place automaticaly, and if the user typed 4 digits in the cell the next cell in the row has to get the "focus".
Any Code it's highly appreciated.
Thank You!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006
Hello Iszatmary,

you could try

-open the vb editor with alt+f11
-next look for the sheet object in the left pane you need to do this for
-doubleclick and paste this code in the code pane

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ThisColumn As Integer
Dim UserInput As Variant
Dim NewInput  As Variant

    ThisColumn = Target.Column
    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
    If ThisColumn < 6 Then
      Target.Offset(0, 1).Activate
      Target.Offset(1, -6).Activate
    End If
End Sub

-it's as close as you can get with normal excel sheet events
-this will check the input when the user tabs/enters out the cell
-it will reformat the time entered, and i made some assumption that after column F = 6 it will move to the first cell on the next row
-this can all be changed also there's no input checking

let me know if this fits or not


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial