cmpgeek
01-07-2005, 07:29 AM
hey yall,
i have a stime sheet set up where i log all time activity and estimate how much time off someone will have months down the road, etc. i got an awesome piece of coding from the KB here and adjusted it to fit my needs and all has gone fairly well. but there is something i have noticed and i cant figure out #1 why Excel feels the need to do this, and #2 how to get it to stop doing it...
here is the coding:
Option Explicit
Sub MyWsChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("H:U")) Is Nothing Then Exit Sub
On Error GoTo errHandler:
With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
.NumberFormat = "[h]:mm"
Case 1 To 99
.Value = TimeSerial(0, .Value, 0)
.NumberFormat = "[h]:mm"
Case 100 To 9999
.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormat = "[h]:mm"
Case Else
End Select
End If
End With
errHandler:
Application.EnableEvents = True
End Sub
99.9% of the time i dont have to worry about a thing, but once the hours get so high Excel starts changing things. For example: when i type in 8000 it is supposed to change it so 80:00 to signify <sp?> 80 hours (a typical pay period for a full time employee). However, when i hit enter, instead of changing it to 80:00 it changes it to 0:03. i have also noticed that when i have a formula set up to add several cells together that it will sometimes do this same thing. In those instances, not only does it convert the number this way, but it also replaces the formula with a date and time value.
Why does Excel do this, and is there anyway to get around it?
thanks yall!
i have a stime sheet set up where i log all time activity and estimate how much time off someone will have months down the road, etc. i got an awesome piece of coding from the KB here and adjusted it to fit my needs and all has gone fairly well. but there is something i have noticed and i cant figure out #1 why Excel feels the need to do this, and #2 how to get it to stop doing it...
here is the coding:
Option Explicit
Sub MyWsChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("H:U")) Is Nothing Then Exit Sub
On Error GoTo errHandler:
With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
.NumberFormat = "[h]:mm"
Case 1 To 99
.Value = TimeSerial(0, .Value, 0)
.NumberFormat = "[h]:mm"
Case 100 To 9999
.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormat = "[h]:mm"
Case Else
End Select
End If
End With
errHandler:
Application.EnableEvents = True
End Sub
99.9% of the time i dont have to worry about a thing, but once the hours get so high Excel starts changing things. For example: when i type in 8000 it is supposed to change it so 80:00 to signify <sp?> 80 hours (a typical pay period for a full time employee). However, when i hit enter, instead of changing it to 80:00 it changes it to 0:03. i have also noticed that when i have a formula set up to add several cells together that it will sometimes do this same thing. In those instances, not only does it convert the number this way, but it also replaces the formula with a date and time value.
Why does Excel do this, and is there anyway to get around it?
thanks yall!