VBA to change decimal place to colon when entering values for time

[Sunjinsak]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’ve been searching for a solution to this for a while.
<o></o>
I’m using Excel 2003 on Win XP SP3.
<o></o>
What I want to do is use VBA to automatically format a decimal place to a colon when entering a value for time.
<o></o>
I have a workbook I made in work where I can input time spent on certain activities. There is a separate worksheet for each month. The sheets then add up the time spent on each activity for each month.
<o></o>
I have formatted the cells where the entries will go to the [h]:mm format but this has a drawback – namely if I enter 1 (for 1 hour) it formats it to 24:00 (for 1 day). I can get around this by manually entering the colon but given the amount of entries I need to make on a daily basis this is somewhat bothersome – it’s much faster to use the numeric keypad and do away with the need to enter a colon manually.
<o></o>
Also if I want to enter 1 hour and 50 mins I have to enter it as 1:50 – again manually entering the colon. I want to be able to just enter 1.5 and, instead of the sheet auto formatting it to 36:00 (1.5 days), I want to have the decimal place converted to a colon and the 5 after the point recognised as 50mins.
<o></o>
Am I making sense?
<o></o>
Basically what I want is this;
<o></o>
1 = 1:00
1. = 1:00
1.00 = 1:00
1.05 = 1:05
1.3 = 1:30
1.45 = 1:45
1.5 = 1:50
2.22 = 2:22
Etc… you get the point.
<o></o>
So no matter how I enter the time using a decimal place (or potentially not using one at all for whole hours) it will intelligently convert it to the [h]:mm format.
<o></o>
I know this is possible as we have a similar sheet where this happens. This sheet was made and is administered centrally though and the VBA code is password protected so I can’t get in to see how it’s done.
<o></o>
I have two named ranges on each sheet where entries will be made – indTime and hsTime.
<o></o>
I know I have to attach the code to a worksheet_change(byval Target as Range) event and I also have to fix the constant loop problem when changing Target.Value by using Application.EnableEvents = True/False to break the code.
<o></o>
I just don’t know how to go about achieving what I want from there.
<o></o>
One thing to note – I don’t want to convert to 24hr values. So for example 1.00 will always equal 1:00 and never 13:00 etc
<o></o>
I hope I’ve not confused the issue too much and my apologies for the long post.
<o></o>
Thanks in advance.

 

[lman715]

What format is the original data in text, numeric?

 

[Scott Huish]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Union(Range("indTime"), Range("hsTime")))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        c = Replace(Format(Val(c), "0.00"), ".", "")
        c = TimeValue(Format(c, "00\:00"))
        c.NumberFormat = "h:mm"
    Next
Application.EnableEvents = True
End Sub

 

[Sunjinsak]

Thanks Hotpepper that works and is just what I'm looking for... ish!

The only problem I have now though is that all entries are preceded by a 0 (e.g. 1:00 now appears as 01:00) - which makes it harder to pick out specific times when reading the monthly sheet and also if I make an entry in the wrong box and need to delete it it inserts 00:00 in the box instead of leaving it blank.

I've also noticed that if I do still enter the colon manually then it changes 1:00 to 00:04. This isn't ideal as I will not be the only one using the sheet once it's complete and someone may well enter times by manually entering the colon.

Sorry to be awkward but is there a way to refine the code to achieve what I want? Like I said in my original post I know it's possible as we have another sheet that does exactly what I'm looking to do - I just can't view the code to see how it's done

Thanks for the help so far though. Much appreciated.

 

[Sunjinsak]

Anyone?

Sorry for the unceremonious bump - I'll only do it once.

This is driving me insane though. I can't find a solution anywhere

Thanks.