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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

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
Top