Excel 2007

[mcverdan]

Hi All. I am trying to type in excel 2007 the time 520 without the colon. Is there anyway that if you hit enter it would automatically be like this 5:20? I've been trying to set it in the time format but it would give me a result like 0 or with date. Can anybody help me how to fix it? Would really appreciate it.

 

[doofusboy]

This would work:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Target.Value = Mid(Target.Value, 1, Len(Target.Value) - 2) & ":" & Right(Target.Value, 2)
    Application.EnableEvents = True
End Sub

 

[mcverdan]

Hi. Are you gonna do it on macro? sorry im not familiar yet with macro. Can you help me and guide me how to do it on macro so i can do it too? Thanks so much for the help.

 

[Yahya]

Hi. Are you gonna do it on macro? sorry im not familiar yet with macro. Can you help me and guide me how to do it on macro so i can do it too? Thanks so much for the help.

hi
in this case
all what you need to do
1. Right Click on sheet name .
2. View Code .
3. past cod to new screen.
4.close this window, or hit Alt+F11

then try the code

 

[mcverdan]

Yay! Wow! Got it thanks so much my gosh!! been doing this for a week now whew! thanks for the help guys! God Bless

 

[mcverdan]

Hi follow-up question.

If you save and close the excel file do you have to go to viewcode and paste it again if you want to use the same file format? thanks again.

 

[Yahya]

Hi follow-up question.

If you save and close the excel file do you have to go to viewcode and paste it again if you want to use the same file format? thanks again.

no
but in Excel 2007 and 2010 you should save your file As Xlsm Not Xlsx

when you save your file as Xlsx you will get this msg

 

[mcverdan]

Ok i'll try it again.. thank you so much