Worksheet change vba involving Times

[Ross3665]

Hi

I have a sheet where users need to enter lots of times in hh:mm:ss formatted cell. To speed up data entry I added a Worksheet change macro to replace '.' with ':', .i.e. so users can enter as hh.mm.ss.

The line that does this is:

Target.Value = Replace(Target.Value, ".", ":") ' For entering time values more easily (required time format)

However sometimes people might not follow this and still enter as hh:mm:ss, or edit a mistake after code already set initial entry to hh:mm:ss. When they do this for some strange reason this code has an unexpected and undesired effect. Say I enter 10:10:10, after the code runs it changes to 0:4237...

I have tried formatting to Target.NumberFormat = "[h]:mm:ss" but that does not help. Also I tried an If clause of where If Not(Target.Value) Like "*:*" but that doesn't seem to work properly either.

Any explanation as to what is happening and how to resolve?

Thanks, Ross

 

[Colin Legg]

The problem is that times in Excel are really numbers. If the cell already has a valid time, despite how it looks in the cell, it will be a number. For example, 02:57:07 is really the number 0.123. If your code tries to replace the decimal point of a valid number (time) then it will become 0:123.

You can add a check into your code to test if the cell contains a valid number:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    'we're only interested in column A
    'we'll only correct 1st cell if multiple were changed
    If Not Intersect(Target.Cells(1), Range("A:A")) Is Nothing Then
 
        'make sure we don't already have a valid time
        If Not IsNumeric(Target.Cells(1)) Then
            Target.Cells(1).Value = Replace(Target.Cells(1).Value, ".", ":")
        End If
    End If
 
End Sub

 

[Ross3665]

Thanks, that seems to solve the problem!