Time Colon VBA

[Brian from Maui]

Trying to revise a code to automatically add a colon and decimal point.

Cell is Custom Formatted as [mm]:ss.00

Want to just enter six digits and the colon and decimal point would be added with the code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("timeCells")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    With Target
    If Len(Target) = 6 Then .Value = Left(.Value, 2) & ":" & Mid(.Value, 2) & "." & Right(.Value, 2)
    Application.EnableEvents = True
    End With
End Sub

Obviuosly not not working. Can someone correct this? TIA

 

[WarPigl3t]

Don't use mid because it will display the third, fourth, fifth, and sixth character before the period.
Use left(mid(.value,2),2)&"."

 

[Brian from Maui]

For some reason when I enter 182200, it changes to 19:22.00

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("timeCells")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    With Target
    If Len(Target) = 6 Then .Value = Left(.Value, 2) & ":" & Left(Mid(.Value, 2), 2) & "." & Right(.Value, 2)
    Application.EnableEvents = True
    End With
End Sub

 

[shg]

Maybe ...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rInt        As Range
    Dim cell        As Range

    Set rInt = Intersect(Target, Me.Range("timeCells"))
    If Not rInt Is Nothing Then
        On Error GoTo Oops
        Application.EnableEvents = False
        
        For Each cell In rInt
            cell.Value = Evaluate("--""0:" & WorksheetFunction.Text(cell.Value, "0\:00\.0") & """")
        Next cell

Oops:
        Application.EnableEvents = True
    End If
End Sub

E.g.,

	A​	B​
2​	98:06.7​	98067​
3​	66:01.5​	66015​
4​	20:56.8​	20568​
5​	21:32.3​	20923​
6​	36:16.1​	36161​

I copied values from col B to col A.

 

[joeu2004]

Trying to revise a code to automatically add a colon and decimal point.
Cell is Custom Formatted as [mm]:ss.00
Want to just enter six digits and the colon and decimal point would be added with the code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    If Intersect(Target, Range("timeCells")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each t In Target
        t.Value = Format(t.Value, "0\:00\.00")
    Next
    Application.EnableEvents = True
End Sub

The For Each In Target is use when modifying more than one cell at the same time; for example, paste-special-value.

Note: It probably would be prudent to bulletproof the code with some diligent checks (e.g. IsNumeric) and/or On Error handling.

 

[Brian from Maui]

Maybe ...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rInt        As Range
    Dim cell        As Range

    Set rInt = Intersect(Target, Me.Range("timeCells"))
    If Not rInt Is Nothing Then
        On Error GoTo Oops
        Application.EnableEvents = False
        
        For Each cell In rInt
            cell.Value = Evaluate("--""0:" & WorksheetFunction.Text(cell.Value, "0\:00\.0") & """")
        Next cell

Oops:
        Application.EnableEvents = True
    End If
End Sub

E.g.,

	A​	B​
2​	98:06.7​	98067​
3​	66:01.5​	66015​
4​	20:56.8​	20568​
5​	21:32.3​	20923​
6​	36:16.1​	36161​

<TBODY>
</TBODY>

I copied values from col B to col A.

When I enter 182200, I get 26236800:00.00.

Seems to be using the 182200 as days.........

 

[Brian from Maui]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    If Intersect(Target, Range("timeCells")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each t In Target
        t.Value = Format(t.Value, "0\:00\.00")
    Next
    Application.EnableEvents = True
End Sub

The For Each In Target is use when modifying more than one cell at the same time; for example, paste-special-value.

Note: It probably would be prudent to bulletproof the code with some diligent checks (e.g. IsNumeric) and/or On Error handling.

Thanks joeu, but I'm a nob when it comes to VBA. I sort of revised this code I had.

 

[Brian from Maui]

I was Custom Formatting the cells as

"00\:00\.00"

then converting with =TEXT(A1,"00\:00\.00")+0

which works OK, but the person I'm helping just wanted to enter numbers in a cell and not convert.

 

[shg]

When I enter 182200, I get 26236800:00.00

When I enter 182200 and the cell is formatted as [m]:ss.0, I get 182:20.0

 

[joeu2004]

Note: It probably would be prudent to bulletproof the code with some diligent checks (e.g. IsNumeric) and/or On Error handling.

Thanks joeu, but I'm a nob when it comes to VBA. I sort of revised this code I had.

If you are asking how to bulletproof the code I suggested, try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    If Intersect(Target, Range("timeCells")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo oops
    For Each t In Target
        t.Value = Format(t.Value, "0\:00\.00")
nexttarget:
    Next
    Application.EnableEvents = True
    Exit Sub
oops:
    Resume nexttarget
End Sub