Worksheet change vba involving Times

Ross3665

New Member
Joined
Mar 12, 2010
Messages
16
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,214,780
Messages
6,121,525
Members
449,037
Latest member
tmmotairi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top