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?

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?

Look he

http://xldynamic.com/source/xld.QDEDownload.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"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?

First, how you format a cell doesn't affect how XL "accepts" a
subsequent entry (except if you set it to Text, which bypasses the
parser).

See

http://cpearson.com/excel/DateTimeEntry.htm

for one technique that you could modify (e.g., by removing punctuation
first).


In article ,
Nevets wrote:

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?

Sorry, I guess I wasn't clear about my own level of expertise (i.e. very
little). I have no idea how to implement your suggestion. Would you mind
more of a step-by-step answer?

Thanks.

"Roger Govier" wrote:

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?

Thanks for trying, but I am trying to do this in an environment where I can't
install a program on my own desktop, never mind on the dozens that I would
need to install this plug-in for it to work for all those who would need to
have it. Users here don't have admin rights.

"Niek Otten" wrote:

Look he

http://xldynamic.com/source/xld.QDEDownload.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"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?

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?

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?

Hi

There may well be more efficient ways, but this seems to work

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tlen As Long, result As String
If Target.Column < 3 Or Target.Column 15 Then Exit Sub
If Target.Column 6 And Target.Column < 12 Then Exit Sub
If Target.Row < 8 Or Target.Row 15 Then Exit Sub

tlen = Len(Target.Value)
Application.EnableEvents = False
If tlen = 1 And Target.Value = 0 Then GoTo endtime

If Left(Target.Value, 1) = "-" Then
MsgBox ("Cannot have negative values")
Target.Value = ""
GoTo endtime
End If

If tlen < 3 Then
MsgBox ("Invalid Entry")
Target.Value = ""
GoTo endtime
End If

If Format(Target.Value, "hh:mm") <= "23:59" And _
Format(Target.Value, "hh:mm") "00:00" Then GoTo endtime

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 = result
endtime:
Target.Value = Format(Target.Value, "hh:mm")

If Format(Target.Value, "hh:mm") = "00:00" Then
Select Case MsgBox("Did you really mean to enter " _
& vbCrLf & "a time of Midnight 00:00?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Query Time input")

Case vbYes

Case vbNo
Target.Value = ""
End Select

End If
Application.EnableEvents = True
End Sub

--
Regards
Roger Govier



"Nevets" wrote in message
...
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?

Getting really close...

It seems to work great, but if I try to clear the contents of a cell or
multiple cells, I get error messages.
If I select a single cell and hit "Clear Contents", I get the "Invalid
Entry" message box.
If I select several cells at once and hit "Clear Contents", I get:
Run-time error '13':
Type mismatch
and the options to End or Debug.
For either of these situations, if I hit "OK" (or "End"), then I can
continue, but I'd prefer it if these messages didn't appear.
If I hit "Debug" in the second example, then the V-Basic screen opens, and
the following line is highlighted:
tlen = Len(Target.Value)

Any ideas?

Thanks.

"Roger Govier" wrote:

Hi

There may well be more efficient ways, but this seems to work

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tlen As Long, result As String
If Target.Column < 3 Or Target.Column 15 Then Exit Sub
If Target.Column 6 And Target.Column < 12 Then Exit Sub
If Target.Row < 8 Or Target.Row 15 Then Exit Sub

tlen = Len(Target.Value)
Application.EnableEvents = False
If tlen = 1 And Target.Value = 0 Then GoTo endtime

If Left(Target.Value, 1) = "-" Then
MsgBox ("Cannot have negative values")
Target.Value = ""
GoTo endtime
End If

If tlen < 3 Then
MsgBox ("Invalid Entry")
Target.Value = ""
GoTo endtime
End If

If Format(Target.Value, "hh:mm") <= "23:59" And _
Format(Target.Value, "hh:mm") "00:00" Then GoTo endtime

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 = result
endtime:
Target.Value = Format(Target.Value, "hh:mm")

If Format(Target.Value, "hh:mm") = "00:00" Then
Select Case MsgBox("Did you really mean to enter " _
& vbCrLf & "a time of Midnight 00:00?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Query Time input")

Case vbYes

Case vbNo
Target.Value = ""
End Select

End If
Application.EnableEvents = True
End Sub

--
Regards
Roger Govier



"Nevets" wrote in message
...
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?

Hi

You don't expect to be able to delete values as well do you? <vbg
Sorry, I rushed it out to you and didn't think it through properly.

Rather than posting all the code again, just copy the following lines

If Target.Count 1 Then Exit Sub
tlen = Len(Target.Value)
If tlen = 0 Then Exit Sub

and paste between

If Target.Row < 8 Or Target.Row 15 Then Exit Sub

<------ Paste lines here

Application.EnableEvents = False

Hopefully this will sort things out for you.

--
Regards
Roger Govier



"Nevets" wrote in message
...
Getting really close...

It seems to work great, but if I try to clear the contents of a cell or
multiple cells, I get error messages.
If I select a single cell and hit "Clear Contents", I get the "Invalid
Entry" message box.
If I select several cells at once and hit "Clear Contents", I get:
Run-time error '13':
Type mismatch
and the options to End or Debug.
For either of these situations, if I hit "OK" (or "End"), then I can
continue, but I'd prefer it if these messages didn't appear.
If I hit "Debug" in the second example, then the V-Basic screen opens, and
the following line is highlighted:
tlen = Len(Target.Value)

Any ideas?

Thanks.

"Roger Govier" wrote:

Hi

There may well be more efficient ways, but this seems to work

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tlen As Long, result As String
If Target.Column < 3 Or Target.Column 15 Then Exit Sub
If Target.Column 6 And Target.Column < 12 Then Exit Sub
If Target.Row < 8 Or Target.Row 15 Then Exit Sub

tlen = Len(Target.Value)
Application.EnableEvents = False
If tlen = 1 And Target.Value = 0 Then GoTo endtime

If Left(Target.Value, 1) = "-" Then
MsgBox ("Cannot have negative values")
Target.Value = ""
GoTo endtime
End If

If tlen < 3 Then
MsgBox ("Invalid Entry")
Target.Value = ""
GoTo endtime
End If

If Format(Target.Value, "hh:mm") <= "23:59" And _
Format(Target.Value, "hh:mm") "00:00" Then GoTo endtime

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 = result
endtime:
Target.Value = Format(Target.Value, "hh:mm")

If Format(Target.Value, "hh:mm") = "00:00" Then
Select Case MsgBox("Did you really mean to enter " _
& vbCrLf & "a time of Midnight 00:00?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Query Time input")

Case vbYes

Case vbNo
Target.Value = ""
End Select

End If
Application.EnableEvents = True
End Sub

--
Regards
Roger Govier



"Nevets" wrote in message
...
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?

By Jove, I think you've got it!!

Thanks for your help. I really wish I understood this stuff better than I
do. It's great having resources like you to help.

Do you know of any V-Basic resources I might be able to read in order to
better understand this type of thing, and do this myself in future? Remember
I'm coming at this from ground zero.

"Roger Govier" wrote:

Hi

You don't expect to be able to delete values as well do you? <vbg
Sorry, I rushed it out to you and didn't think it through properly.

Rather than posting all the code again, just copy the following lines

If Target.Count 1 Then Exit Sub
tlen = Len(Target.Value)
If tlen = 0 Then Exit Sub

and paste between

If Target.Row < 8 Or Target.Row 15 Then Exit Sub

<------ Paste lines here

Application.EnableEvents = False

Hopefully this will sort things out for you.

--
Regards
Roger Govier



"Nevets" wrote in message
...
Getting really close...

It seems to work great, but if I try to clear the contents of a cell or
multiple cells, I get error messages.
If I select a single cell and hit "Clear Contents", I get the "Invalid
Entry" message box.
If I select several cells at once and hit "Clear Contents", I get:
Run-time error '13':
Type mismatch
and the options to End or Debug.
For either of these situations, if I hit "OK" (or "End"), then I can
continue, but I'd prefer it if these messages didn't appear.
If I hit "Debug" in the second example, then the V-Basic screen opens, and
the following line is highlighted:
tlen = Len(Target.Value)

Any ideas?

Thanks.

"Roger Govier" wrote:

Hi

There may well be more efficient ways, but this seems to work

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tlen As Long, result As String
If Target.Column < 3 Or Target.Column 15 Then Exit Sub
If Target.Column 6 And Target.Column < 12 Then Exit Sub
If Target.Row < 8 Or Target.Row 15 Then Exit Sub

tlen = Len(Target.Value)
Application.EnableEvents = False
If tlen = 1 And Target.Value = 0 Then GoTo endtime

If Left(Target.Value, 1) = "-" Then
MsgBox ("Cannot have negative values")
Target.Value = ""
GoTo endtime
End If

If tlen < 3 Then
MsgBox ("Invalid Entry")
Target.Value = ""
GoTo endtime
End If

If Format(Target.Value, "hh:mm") <= "23:59" And _
Format(Target.Value, "hh:mm") "00:00" Then GoTo endtime

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 = result
endtime:
Target.Value = Format(Target.Value, "hh:mm")

If Format(Target.Value, "hh:mm") = "00:00" Then
Select Case MsgBox("Did you really mean to enter " _
& vbCrLf & "a time of Midnight 00:00?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Query Time input")

Case vbYes

Case vbNo
Target.Value = ""
End Select

End If
Application.EnableEvents = True
End Sub

--
Regards
Roger Govier



"Nevets" wrote in message
...
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?

Hi

Most of what I have learnt over the past xx years, has been through reading
the postings in the various groups, trying them out for myself, then
figuring out how they work.
For VBA, then the .programming NG is the place to hang out.

As far as books are concerned, I have
Excel 2003 Power Programming with VBA by John Walkenbach
Professional Excel Development by Bullen, Bovey and Green
Excel Programming Weekend Crash Course by Peter G Aitken.

amongst others, but only because I am too mean to spend the money I should
on other excellent books. Watching and trying techniques in the NG's is as
good a way of learning as any, IMO.

Debra Dalgleish has a much more extensive list at her site
http://www.contextures.com/xlPivot08.html


--
Regards
Roger Govier



"Nevets" wrote in message
...
By Jove, I think you've got it!!

Thanks for your help. I really wish I understood this stuff better than I
do. It's great having resources like you to help.

Do you know of any V-Basic resources I might be able to read in order to
better understand this type of thing, and do this myself in future?
Remember
I'm coming at this from ground zero.

"Roger Govier" wrote:

Hi

You don't expect to be able to delete values as well do you? <vbg
Sorry, I rushed it out to you and didn't think it through properly.

Rather than posting all the code again, just copy the following lines

If Target.Count 1 Then Exit Sub
tlen = Len(Target.Value)
If tlen = 0 Then Exit Sub

and paste between

If Target.Row < 8 Or Target.Row 15 Then Exit Sub

<------ Paste lines here

Application.EnableEvents = False

Hopefully this will sort things out for you.

--
Regards
Roger Govier



"Nevets" wrote in message
...
Getting really close...

It seems to work great, but if I try to clear the contents of a cell or
multiple cells, I get error messages.
If I select a single cell and hit "Clear Contents", I get the "Invalid
Entry" message box.
If I select several cells at once and hit "Clear Contents", I get:
Run-time error '13':
Type mismatch
and the options to End or Debug.
For either of these situations, if I hit "OK" (or "End"), then I can
continue, but I'd prefer it if these messages didn't appear.
If I hit "Debug" in the second example, then the V-Basic screen opens,
and
the following line is highlighted:
tlen = Len(Target.Value)

Any ideas?

Thanks.

"Roger Govier" wrote:

Hi

There may well be more efficient ways, but this seems to work

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tlen As Long, result As String
If Target.Column < 3 Or Target.Column 15 Then Exit Sub
If Target.Column 6 And Target.Column < 12 Then Exit Sub
If Target.Row < 8 Or Target.Row 15 Then Exit Sub

tlen = Len(Target.Value)
Application.EnableEvents = False
If tlen = 1 And Target.Value = 0 Then GoTo endtime

If Left(Target.Value, 1) = "-" Then
MsgBox ("Cannot have negative values")
Target.Value = ""
GoTo endtime
End If

If tlen < 3 Then
MsgBox ("Invalid Entry")
Target.Value = ""
GoTo endtime
End If

If Format(Target.Value, "hh:mm") <= "23:59" And _
Format(Target.Value, "hh:mm") "00:00" Then GoTo endtime

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 = result
endtime:
Target.Value = Format(Target.Value, "hh:mm")

If Format(Target.Value, "hh:mm") = "00:00" Then
Select Case MsgBox("Did you really mean to enter " _
& vbCrLf & "a time of Midnight 00:00?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Query Time input")

Case vbYes

Case vbNo
Target.Value = ""
End Select

End If
Application.EnableEvents = True
End Sub

--
Regards
Roger Govier



"Nevets" wrote in message
...
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?