Code for entering time

D

Denise

I have a code in my workbook so that when you enter time you do not have to
enter the colon, you just enter 1230 and it will format as 12:30. The
workbook is used by about ten different people and is on a network drive. It
works fine on my computer but when another user opens the workbook on their
computer it does not work. When they enter the time it comes up as 00:00.

Can anyone tell me what is going on? Here is the code that I am using:


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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("G4:K8000")) 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
 
D

Denise

That is where I got the code. I just done a copy and paste and then changed
the range.

David McRitchie said:
Hi Denise,
Where did you get that?

Try Chip Pearson's page
Times, Quick Entry (topic)
http://www.cpearson.com/excel/DateTimeEntry.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Denise said:
I have a code in my workbook so that when you enter time you do not have to
enter the colon, you just enter 1230 and it will format as 12:30. The
workbook is used by about ten different people and is on a network drive. It
works fine on my computer but when another user opens the workbook on their
computer it does not work. When they enter the time it comes up as 00:00.

Can anyone tell me what is going on? Here is the code that I am using:


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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("G4:K8000")) 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
Click to expand...
Click to expand...
 
D

David McRitchie

Hi Denise,
So it is. I just glanced at the code misreading the comments for code.
and it looked strange.

Select a cell before entry,
what do you see on the formula bar (better be empty), and what for format, cells, custom

Select same cell after entry,
what do you see on the formula bar, and what for format, cells, custom

what happens if the enter into a cell elsewhere
100.14
100
1214
10000. (decimal point)
If they don't see the same numbers and see something like
100.14 10.00 12.14 10000 then Fixed decimal is in effect.
tools, options, edit, uncheck Fixed Decimal in the middle of the dialog.
the number can be positive or negative but unless you are an accountant you don't
want that checked. And I don't know how accountants work with it anyway it's all or nothing.




What version of Excel.
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Denise said:
That is where I got the code. I just done a copy and paste and then changed
the range.

David McRitchie said:
Hi Denise,
Where did you get that?

Try Chip Pearson's page
Times, Quick Entry (topic)
http://www.cpearson.com/excel/DateTimeEntry.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Denise said:
I have a code in my workbook so that when you enter time you do not have to
enter the colon, you just enter 1230 and it will format as 12:30. The
workbook is used by about ten different people and is on a network drive. It
works fine on my computer but when another user opens the workbook on their
computer it does not work. When they enter the time it comes up as 00:00.

Can anyone tell me what is going on? Here is the code that I am using:


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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("G4:K8000")) 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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

What number do they enter?

What shows up in the formula bar when they're done?
 
D

Denise

I can enter the time like the following

1600 and it will format to 16:00. This is on my computer.

On another user they can type in
1600 and it will come up 0:00
In the formula bar it shows - 5/18/1904 12:00am
 
D

Dave Peterson

It sounds like the macro isn't running.

My first guess is that the user either disabled macros when they opened the
file--or they have their security settings set that macros are disabled (and
never see the prompt).

Tools|macro|security|security level is where I'd start.

Yelling at the user to enable macros is where I'd finish <vbg>.

(Since the macro isn't running, when you format that cell as a date/time, excel
sees 1600 as 1600 days past 12/31/1899 which happens to be 5/18/1904.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

please help.is it possible re-edit macro to convert text like1530-2200 to time format automatically? 1
Out of Stack Space error 2
Time with VBA code 8
Time Input Mask 0
Date formatting...Why is it not working??? 2
Time entry with VBA 3
Code for entering time - trying again 4
Want Fixed Decimal places in only 1 column 4

Top