Roger,
I think we're getting closer; I got the View Code Visual Basic to display,
and I just copied and pasted your instructions into the pane at the right. I
then changed the line:
"If Target.Column < 10 Then Exit Sub" to
"If Target.Column < 3 Or Target.Column 15 Then Exit Sub"
since the multiple cells I want this rule to apply in are in columns 3-6 and
12-15, rows 8 through 15.

Two problems arise:
1. If someone actually enters a time with the correct hh:mm form, they get
the error message "Invalid Entry".
2. There are cells in the above range that I don't want the rule to apply
in. For example, I've got some merged cells where I want to enter text, not
times. When I enter text in those, I get the "Can't Enter a Time Past 23:59"
error message. Can I make the rule apply to specific cells, not entire
columns?

"Roger Govier" wrote:

Hi

Right click on the sheet tab where you enter data and choose View code.
Copy the routine into white pane that appears.
Click back onto your Excel Sheet and as you enter data into the column you
set for the code to be activated, the values entered as say 845 will be
converted to 08:45

--
Regards
Roger Govier



"Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message
...
Hi

One way would be to use some event code on the sheet where the times are
being entered.

Change the Target column number to suit the column number where you are
entering the time data.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tlen As Long, result As String
If Target.Column < 10 Then Exit Sub <--- Change column number to suit
tlen = Len(Target.Value)
Application.EnableEvents = False
If tlen < 3 Or tlen 4 Then
MsgBox ("Invalid Entry")
GoTo endtime
End If

If Target.Value 2359 Then
MsgBox ("Can't enter a time past 23:59")
GoTo endtime
End If

If tlen = 3 Then
result = Left(Target.Value, 1) & ":" & Right(Target.Value, 2)
Else
result = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
End If
Target.Value = Format(result, "hh:mm")
endtime:
Application.EnableEvents = True
End Sub
--
Regards
Roger Govier



"Nevets" wrote in message
...
I am creating a workbook for many users, so I would like to make it as
"idiot
proof" as possible. In several of the cells, I want users to enter time
in a
24 hr format. If I format the cell to accept "hh:mm" times, then the
users
have to enter it exactly that way for it to be accepted. Is there a way
to
set up the cell so that if they enter "hhmm", or "hh.mm", (or even "hmm"
if
the time being entered is before 0959), the program will convert it to
"hh:mm" format when they tab out of the cell?
For example, user wants to enter 08:00 hrs; so if they type 08:00, 08.00,
0800, or 800 in the cell, when they tab out, it will convert to 08:00.
I also want to do something similar with a date field. MS Word does
something like this with text form fields; I can set up a text field such
that if someone enters "sep 7 07" then tabs out of the field, it will be
changed to "Friday September 7, 2007".
Can I get Excel to do what I want?