I want to be able to enter a time, using "." instead of ":", so that
when I enter the number, then press "Enter", the number changes from,
say, 13.27.33 to 13:27:33. But I am having to reselect the cell to get
it to change. What is wrong with my code?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Replace What:=".", Replacement:=":", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.NumberFormat = "h:mm:ss"
End Sub

Thanks,

Tonso

Wrong event. Try this one.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Replace What:=".", Replacement:=":", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Target.NumberFormat = "h:mm:ss"
End Sub


Gord Dibben MS Excel MVP

On Fri, 2 Apr 2010 07:55:51 -0700 (PDT), Tonso
wrote:

I want to be able to enter a time, using "." instead of ":", so that
when I enter the number, then press "Enter", the number changes from,
say, 13.27.33 to 13:27:33. But I am having to reselect the cell to get
it to change. What is wrong with my code?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Replace What:=".", Replacement:=":", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.NumberFormat = "h:mm:ss"
End Sub

Thanks,

Tonso

On Apr 2, 11:14*am, Gord Dibben <gorddibbATshawDOTca wrote:
Wrong event. *Try this one.

Private Sub Worksheet_Change(ByVal Target As Range)
* * Target.Replace What:=".", Replacement:=":", LookAt:=xlPart, _
* * * * SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
* * * * ReplaceFormat:=False
* * Target.NumberFormat = "h:mm:ss"
End Sub

Gord Dibben *MS Excel MVP

On Fri, 2 Apr 2010 07:55:51 -0700 (PDT), Tonso
wrote:



I want to be able to enter a time, using "." instead of ":", so that
when I enter the number, then press "Enter", the number changes from,
say, 13.27.33 to 13:27:33. But I am having to reselect the cell to get
it to change. What is wrong with my code?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
* *Selection.Replace What:=".", Replacement:=":", LookAt:=xlPart, _
* * * *SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
* * * *ReplaceFormat:=False
* *Selection.NumberFormat = "h:mm:ss"
End Sub

Thanks,

Tonso- Hide quoted text -

- Show quoted text -

As always Gord, thanks so much. it works perfectlty!!!

Tonso