| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Code for entering time - trying again
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. I got the code from this site: Chip Pearson's page Times, Quick Entry (topic) http://www.cpearson.com/excel/DateTimeEntry.htm 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
|
|||
|
|||
|
Just a thought. Did you place the code in your personals workbook? Insert the
module in this workbook HTH "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. I got the code from this site: Chip Pearson's page Times, Quick Entry (topic) http://www.cpearson.com/excel/DateTimeEntry.htm 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
|
|||
|
|||
|
It is in the workbook that is on the network. I went to the worksheet tab,
done a right click and picked View Code. This is where I put in the code. Is this right? I do not know much about code. "Ray A" wrote: Just a thought. Did you place the code in your personals workbook? Insert the module in this workbook HTH "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. I got the code from this site: Chip Pearson's page Times, Quick Entry (topic) http://www.cpearson.com/excel/DateTimeEntry.htm 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
|
|||
|
|||
|
I am not close to the VBA expert either.
The personal workbook is a hidden workbook that can store code (macros) that you have created and want to use on any .xls project. Storing code in the individual workbook means the code can only be used when that workbook is open. Open the VBA editor and go to viewproject explorer. Make sure the code is in the workbook on the share and not in your personal workbook. If it is in your book you can copy into a new module in the book you are working on. HTH "Denise" wrote: It is in the workbook that is on the network. I went to the worksheet tab, done a right click and picked View Code. This is where I put in the code. Is this right? I do not know much about code. "Ray A" wrote: Just a thought. Did you place the code in your personals workbook? Insert the module in this workbook HTH "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. I got the code from this site: Chip Pearson's page Times, Quick Entry (topic) http://www.cpearson.com/excel/DateTimeEntry.htm 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
|
|||
|
|||
|
You have another response at your first post.
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. I got the code from this site: Chip Pearson's page Times, Quick Entry (topic) http://www.cpearson.com/excel/DateTimeEntry.htm 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 |
| 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 | |||
Linear Mode
