| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Code for entering time
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 |
|
#2
|
|||
|
|||
|
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" wrote in message ... 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 |
|
#3
|
|||
|
|||
|
That is where I got the code. I just done a copy and paste and then changed
the range. "David McRitchie" wrote: 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" wrote in message ... 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 |
|
#4
|
|||
|
|||
|
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" wrote in message ... That is where I got the code. I just done a copy and paste and then changed the range. "David McRitchie" wrote: 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" wrote in message ... 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 |
|
#5
|
|||
|
|||
|
What number do they enter?
What shows up in the formula bar when they're done? Denise wrote: 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 -- Dave Peterson |
|
#6
|
|||
|
|||
|
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 "Dave Peterson" wrote: What number do they enter? What shows up in the formula bar when they're done? Denise wrote: 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 -- Dave Peterson |
|
#7
|
|||
|
|||
|
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.) Denise wrote: 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 "Dave Peterson" wrote: What number do they enter? What shows up in the formula bar when they're done? Denise wrote: 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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| My Quest with Time!! | Excel Worksheet Functions | |||
| How to use solver to schedule part time and full time workforce? | Excel Discussion (Misc queries) | |||
| time math | Excel Worksheet Functions | |||
| Time Sheets | New Users to Excel | |||
| subtraction of times, convert & multiply by a conditioned rate | Excel Worksheet Functions | |||
Hybrid Mode
