Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Timesheet
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Timesheet
Hi,

I want that in my timesheet, when i type 024 that it then display 0:24 in
the cell, and then that i can use them to do my calculation in my timesheet.
I've found some code but it can't manage hours between midnight and 1:00 AM

Michael

There's the code i found, but still dont do it:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("Hours")) 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 = 1:00 AM
TimeStr = .Value & ":00"
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = .Value & ":00"
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 "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 95
Default Timesheet
If the cell is formatted as numeric or general, it will strip the leading
zero(es). 024 becomes 24 (and the time will be 24:00). 007 becomes 7 (and the
time will be 7:00).
--
Adios,
Clay Harryman


"Construk" wrote:

Hi,

I want that in my timesheet, when i type 024 that it then display 0:24 in
the cell, and then that i can use them to do my calculation in my timesheet.
I've found some code but it can't manage hours between midnight and 1:00 AM

Michael

There's the code i found, but still dont do it:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("Hours")) 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 = 1:00 AM
TimeStr = .Value & ":00"
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = .Value & ":00"
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 "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Timesheet
Try this:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("C:C")) 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
Debug.Print Len(.Value)
Select Case Len(.Value * 1)
Case 1 ' e.g., 1 = 1:00 AM
TimeStr = "0:0" & .Value * 1
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = "0:" & .Value * 1
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)
.NumberFormat = "hh:mm"
End If

End With
Application.EnableEvents = True

Exit Sub
EndMacro:
MsgBox "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy


"Construk" wrote:

Hi,

I want that in my timesheet, when i type 024 that it then display 0:24 in
the cell, and then that i can use them to do my calculation in my timesheet.
I've found some code but it can't manage hours between midnight and 1:00 AM

Michael

There's the code i found, but still dont do it:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("Hours")) 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 = 1:00 AM
TimeStr = .Value & ":00"
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = .Value & ":00"
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 "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Timesheet
Just not that to get 11 PM you would enter 2300 rather then 23.

If you want to signal Midnight to 1 AM by the leading zero, then post back.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Try this:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("C:C")) 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
Debug.Print Len(.Value)
Select Case Len(.Value * 1)
Case 1 ' e.g., 1 = 1:00 AM
TimeStr = "0:0" & .Value * 1
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = "0:" & .Value * 1
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)
.NumberFormat = "hh:mm"
End If

End With
Application.EnableEvents = True

Exit Sub
EndMacro:
MsgBox "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy


"Construk" wrote:

Hi,

I want that in my timesheet, when i type 024 that it then display 0:24 in
the cell, and then that i can use them to do my calculation in my timesheet.
I've found some code but it can't manage hours between midnight and 1:00 AM

Michael

There's the code i found, but still dont do it:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("Hours")) 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 = 1:00 AM
TimeStr = .Value & ":00"
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = .Value & ":00"
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 "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Timesheet
Man, you made my day !

Thank you very much !

Michael


"Tom Ogilvy" wrote:

Try this:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("C:C")) 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
Debug.Print Len(.Value)
Select Case Len(.Value * 1)
Case 1 ' e.g., 1 = 1:00 AM
TimeStr = "0:0" & .Value * 1
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = "0:" & .Value * 1
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)
.NumberFormat = "hh:mm"
End If

End With
Application.EnableEvents = True

Exit Sub
EndMacro:
MsgBox "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy


"Construk" wrote:

Hi,

I want that in my timesheet, when i type 024 that it then display 0:24 in
the cell, and then that i can use them to do my calculation in my timesheet.
I've found some code but it can't manage hours between midnight and 1:00 AM

Michael

There's the code i found, but still dont do it:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("Hours")) 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 = 1:00 AM
TimeStr = .Value & ":00"
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = .Value & ":00"
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 "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Timesheet

"Tom Ogilvy" wrote:

Just not that to get 11 PM you would enter 2300 rather then 23.

That's what i want: typing the time as a numeric watch.


If you want to signal Midnight to 1 AM by the leading zero, then post back.

It already works. But if you mean imposing to type it with a zero before
(0:xx) and generating an error if not, then yes i would prefer that. Then it
mean that there is to always be 3 digits to be type, if not, the error
message appears ?! That's ok with me


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Try this:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("C:C")) 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
Debug.Print Len(.Value)
Select Case Len(.Value * 1)
Case 1 ' e.g., 1 = 1:00 AM
TimeStr = "0:0" & .Value * 1
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = "0:" & .Value * 1
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)
.NumberFormat = "hh:mm"
End If

End With
Application.EnableEvents = True

Exit Sub
EndMacro:
MsgBox "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy


"Construk" wrote:

Hi,

I want that in my timesheet, when i type 024 that it then display 0:24 in
the cell, and then that i can use them to do my calculation in my timesheet.
I've found some code but it can't manage hours between midnight and 1:00 AM

Michael

There's the code i found, but still dont do it:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("Hours")) 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 = 1:00 AM
TimeStr = .Value & ":00"
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = .Value & ":00"
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 "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Timesheet
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("C:C")) 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
Debug.Print Len(.Value)
Select Case Len(.Value * 1)
Case 1 ' e.g., 1 = 1:00 AM
if len(.Text) = 1 then err.Raise 0
TimeStr = "0:0" & .Value * 1
Case 2 ' e.g., 12 = 12:00 AM
if len(.text) = 2 then err.Raise 0
TimeStr = "0:" & .Value * 1
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)
.NumberFormat = "hh:mm"
End If

End With
Application.EnableEvents = True

Exit Sub
EndMacro:
MsgBox "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub

Would cause the "Vous devez inscrire vos données comme suit: pour 13:45 ,
inscrire
1345 "
to be shown if a 1 or two digit number is entered without a leading zero
(making it a 2 or 3 digit number).

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote:

Try this:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("C:C")) 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
Debug.Print Len(.Value)
Select Case Len(.Value * 1)
Case 1 ' e.g., 1 = 1:00 AM
TimeStr = "0:0" & .Value * 1
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = "0:" & .Value * 1
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)
.NumberFormat = "hh:mm"
End If

End With
Application.EnableEvents = True

Exit Sub
EndMacro:
MsgBox "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy


"Construk" wrote:

Hi,

I want that in my timesheet, when i type 024 that it then display 0:24 in
the cell, and then that i can use them to do my calculation in my timesheet.
I've found some code but it can't manage hours between midnight and 1:00 AM

Michael

There's the code i found, but still dont do it:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("Hours")) 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 = 1:00 AM
TimeStr = .Value & ":00"
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = .Value & ":00"
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 "Vous devez inscrire vos données comme suit: pour 13:45 , inscrire
1345 "

Application.EnableEvents = True

End Sub
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
timesheet Andy Bolger Excel Discussion (Misc queries) 2 September 23rd 09 12:03 PM
Help with timesheet please klg Excel Worksheet Functions 0 March 10th 08 07:33 PM
Timesheet Matt Excel Discussion (Misc queries) 3 September 15th 05 10:14 PM
Timesheet help kimmyrt Excel Worksheet Functions 3 March 22nd 05 04:34 AM
Timesheet Doug Excel Worksheet Functions 1 March 3rd 05 07:53 PM


All times are GMT +1. The time now is 03:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement