View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
NoodNutt[_2_] NoodNutt[_2_] is offline
external usenet poster
 
Posts: 39
Default Converting Numeral to Time equivalent

Hi Team

Was hoping someone was able to point me in the right direction please:

I am trying to speed up data entry by just entering a number and have in auto-convert to time, eg 515 = 5:15 or 1415 = 14:15, reducing keystrokes will significantly speed up this process in Columns( 10 & 11 ).

I tried inserting it into my Worksheet_Change event, but it will not fire ( although it fires if I run the sub on it's own ).

I have this so far, so if anyone can point me in the right direction, I will be super-grateful.

As always, much appreciation
TIA
Mark.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C3:C43")) Is Nothing Then
If Target.Cells.Count 1 Then Exit Sub

Select Case Target.Column
Case 3
If Target.Value = "YARD" Or Target.Value = "C0654" Then
Target.Offset(0, 7).Select
Else
Target.Offset(0, 1).Select
End If
Case 8
Target.Offset(0, 2).Select
Case 10
NumberToTime
Target.Offset(0, 1).Select
Case 11
NumberToTime
End Select
End If

End Sub
Private Sub NumberToTime()

Dim rCell As Range
Dim iHours As Integer
Dim iMins As Integer

For Each rCell In Selection
If IsNumeric(rCell.Value) And Len(rCell.Value) 0 Then
iHours = rCell.Value \ 100
iMins = rCell.Value Mod 100
rCell.Value = (iHours + iMins / 60) / 24
rCell.NumberFormat = "[h]:mm"
End If
Next
End Sub