LinkBack URL
About LinkBacksI've seen alot of traffic regarding fixed decimal places. I'm aware I can
have fixed decimal places throughout the worksheet; however, I only want
fixed decimal places in one column.
I want to be able to enter 735 and get 7.35
Or 1234 and get 12.34
Or 12345 and get 123.45
So, 2 fixed decimals but only in this column
I have found a VB program on this discussion group in the past that helped
me format time so that I only have to enter 700 and it returns 7:00 AM
or 1300 = 1:00pm.
Since I don't understand VB well, I tried to utilize the same program to do
what I'm requesting above. Below is the VB code which includes the first
range for time entry and NewRange1 where I was attempting to modify the code
for number entry with 2 fixed decimals. Based on the code modifications,
unfortunately, when I enter a number 735, I get 735.35 or 1234 I get 1234.34
Can someone PLEASE look at this code and make the changes to support my
requirements? I will not be entering any numbers longer than the 4 Cases
shown.
Thanks
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.Range)
On Error GoTo EndMacro
If Application.Intersect(Target, Range("C8:C38")) Is Nothing Then
GoTo NewRange1
End If
If Target.Cells.Count > 1 Then
GoTo NewRange1
End If
If Target.Value = "" Then
GoTo NewRange1
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
'Starting new Range
NewRange1:
If Application.Intersect(Target, Range("H8:H38")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 735 = 7.35
TimeStr = Left(.Value, 1) & "." & _
Right(.Value, 2)
Case 2 ' e.g., 1234 = 12.34
TimeStr = Left(.Value, 2) & "." & _
Right(.Value, 2)
Case 3 ' e.g., 12345 = 123.45
TimeStr = Left(.Value, 3) & "." & _
Right(.Value, 2)
Case 4 ' e.g., 123456 = 1234.56
TimeStr = Left(.Value, 4) & "." & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = (TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You Did Not Enter a Valid AM Time; IF You Have Entered PM Time -
Disregard"
Application.EnableEvents = True
End Sub
Register To Reply
Bookmarks