I had asked for a way to go to a cell, enter a value like "1234" and
have that displayed as "00:12:34". I was given the following snippet of
code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Range("E4:E27", "F4:F27")) Is Nothing Then Exit
Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
Case 1 To 99
.Value = TimeSerial(0, 0, .Value)
.NumberFormat = "hh:mm:ss"
Case 100 To 2359
.Value = TimeSerial(0, Int(.Value / 100), .Value Mod
100)
.NumberFormat = "hh:mm:ss"
Case 10000 To 235959
.Value = TimeSerial(Int(.Value / 10000), _
Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case Else
End Select
End If
End With
errHandler:
Application.EnableEvents = True

End Sub


This mostly works, except it seems that if I enter a 4 digit number that
starts with a 3, 4, or 5, I get a weird date.

For example:

3124 gets me 7/20/1908 12:00:00 AM
4567 gets me 7/2/1912 12:00:00 AM

--
John Oliver, CCNA http://www.john-oliver.net/
Linux/UNIX/network consulting http://www.john-oliver.net/resume/
*** sendmail, Apache, ftp, DNS, spam filtering ***
**** Colocation, T1s, web/email/ftp hosting ****


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

John

The problem is that the cell is formatted for time so Excel thinks you're
entering a date. When you enter a date of 3421, you get the date that is
3,421 days from Jan 1, 1900 12:00AM. Under your Case Else statement in your
code, you should put this line

..NumberFormat = "general"

That will show you what you entered when what you enter is not in the format
that's accepted by your code.
--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"John Oliver" wrote in message
.. .
I had asked for a way to go to a cell, enter a value like "1234" and
have that displayed as "00:12:34". I was given the following snippet of
code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Range("E4:E27", "F4:F27")) Is Nothing Then Exit
Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
Case 1 To 99
.Value = TimeSerial(0, 0, .Value)
.NumberFormat = "hh:mm:ss"
Case 100 To 2359
.Value = TimeSerial(0, Int(.Value / 100), .Value Mod
100)
.NumberFormat = "hh:mm:ss"
Case 10000 To 235959
.Value = TimeSerial(Int(.Value / 10000), _
Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case Else
End Select
End If
End With
errHandler:
Application.EnableEvents = True

End Sub


This mostly works, except it seems that if I enter a 4 digit number that
starts with a 3, 4, or 5, I get a weird date.

For example:

3124 gets me 7/20/1908 12:00:00 AM
4567 gets me 7/2/1912 12:00:00 AM

--
John Oliver, CCNA http://www.john-oliver.net/
Linux/UNIX/network consulting http://www.john-oliver.net/resume/
*** sendmail, Apache, ftp, DNS, spam filtering ***
**** Colocation, T1s, web/email/ftp hosting ****


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

John,

There is an error in the code that only allows for 24 minutes, not 60.

Change the line
Case 100 To 2359
to
Case 100 To 5959

--

HTH

Bob Phillips

"John Oliver" wrote in message
.. .
I had asked for a way to go to a cell, enter a value like "1234" and
have that displayed as "00:12:34". I was given the following snippet of
code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Range("E4:E27", "F4:F27")) Is Nothing Then Exit
Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
Case 1 To 99
.Value = TimeSerial(0, 0, .Value)
.NumberFormat = "hh:mm:ss"
Case 100 To 2359
.Value = TimeSerial(0, Int(.Value / 100), .Value Mod
100)
.NumberFormat = "hh:mm:ss"
Case 10000 To 235959
.Value = TimeSerial(Int(.Value / 10000), _
Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case Else
End Select
End If
End With
errHandler:
Application.EnableEvents = True

End Sub


This mostly works, except it seems that if I enter a 4 digit number that
starts with a 3, 4, or 5, I get a weird date.

For example:

3124 gets me 7/20/1908 12:00:00 AM
4567 gets me 7/2/1912 12:00:00 AM

--
John Oliver, CCNA http://www.john-oliver.net/
Linux/UNIX/network consulting http://www.john-oliver.net/resume/
*** sendmail, Apache, ftp, DNS, spam filtering ***
**** Colocation, T1s, web/email/ftp hosting ****


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

On Wed, 6 Aug 2003 21:56:57 +0100, Bob Phillips wrote:
John,

There is an error in the code that only allows for 24 minutes, not 60.

Change the line
Case 100 To 2359
to
Case 100 To 5959

Thank you Bob!!!

--
John Oliver, CCNA http://www.john-oliver.net/
Linux/UNIX/network consulting http://www.john-oliver.net/resume/
*** sendmail, Apache, ftp, DNS, spam filtering ***
**** Colocation, T1s, web/email/ftp hosting ****


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----