Format Cell As You Type

[cooper]

I have formatted certain cells of my spreadsheet to indicate time in the
format hh:mm using the custom cell formattihg option.

With this method, everytime I enter a time say 19:30, I have to insert
a colon using the shift key and all.

I just wanted to enter the figures 1930 and let the cell format itself
to 19:30

I was informed by VBA Gurus to use the following VBA code:


Code:
--------------------

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) 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., 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
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

--------------------


and paste it in the workbook section (I have 25 worksheets in my
workbook and more to come), so that I don't have to paste the code in
all the individual worksheets. I believe the code needs some tweaking
if it has to be placed in the workbook section.

The problem is that it does not work.

Can somebody help me in this regard. FYI, I know nothing about VB
coding.

Thanks.

 

[Norman Jones]

Hi Cooper,

Try changing:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

to

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As
Range)

 

[Norman Jones]

Hi Cooper,

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As
Range)

This should be a single line, broken by the underscore (_) line continuation
character:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)

 

[Mike K]

Cooper,
Forgive me for being too simplistic here, but if you just want
to type in 1930 and have it show up in the cell as 19:30, why don't you just
custom format as ##":"## and you will accomplish the same thing.

Mike

 

[cooper]

If I use Norman's method, I get a compilation error. So Norman, could
you please amend the code and paste it using the 'code' tag. Thanks.

Mike, your suggestion is very good, but then I can't do any time/date
calculations using those cells. I will use it as a last resort.