| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time input in w/sheet #2
I had a problem which was resolved (or so I thought) yesterday regarding
input of time. I was kindly given the solution Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) 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 I changed If Application.Intersect(Target, Range("A1:A10")) Is To If Application.Intersect(Target, Range("a:t")) Is To encompass the whole w/sheet. That works fine in the areas where time is to be entered but I lost my way somewhat and missed the fact that I need to have other columns with a numeric (no decimal) entry. I have tried to specify the cols where time is entered but to no avail. They are H,K,N,Q. Numeric entries are in D,G,J,M,P & S. This is all new to me and Id like some more assistance please -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input in w/sheet #2
Try this instead:
If Application.Intersect(Target, Union(Range("D:D"),Range("G:G"),Range("J:J"), _ Range("M:M"), Range("P:P"), Range("S:S"))) Is Nothing Then "Russell Dawson" wrote: I had a problem which was resolved (or so I thought) yesterday regarding input of time. I was kindly given the solution Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) 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 I changed If Application.Intersect(Target, Range("A1:A10")) Is To If Application.Intersect(Target, Range("a:t")) Is To encompass the whole w/sheet. That works fine in the areas where time is to be entered but I lost my way somewhat and missed the fact that I need to have other columns with a numeric (no decimal) entry. I have tried to specify the cols where time is entered but to no avail. They are H,K,N,Q. Numeric entries are in D,G,J,M,P & S. This is all new to me and Id like some more assistance please -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input in w/sheet #2
Oops, I just realized I grabbed your numeric entry columns instead. Use
references to H, K, N and Q instead of the others in the statement I posted, as: If Application.Intersect(Target, Union(Range("H:H"), Range("K:K"), _ Range("N:N"), Range("Q:Q"))) Is Nothing Then "Russell Dawson" wrote: I had a problem which was resolved (or so I thought) yesterday regarding input of time. I was kindly given the solution Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) 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 I changed If Application.Intersect(Target, Range("A1:A10")) Is To If Application.Intersect(Target, Range("a:t")) Is To encompass the whole w/sheet. That works fine in the areas where time is to be entered but I lost my way somewhat and missed the fact that I need to have other columns with a numeric (no decimal) entry. I have tried to specify the cols where time is entered but to no avail. They are H,K,N,Q. Numeric entries are in D,G,J,M,P & S. This is all new to me and Id like some more assistance please -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input in w/sheet #2
Another one:
If Application.Intersect(Target, me.Range("H:H,K:K,N:N,Q:Q")) Is Nothing Then Russell Dawson wrote: I had a problem which was resolved (or so I thought) yesterday regarding input of time. I was kindly given the solution Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) 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 I changed If Application.Intersect(Target, Range("A1:A10")) Is To If Application.Intersect(Target, Range("a:t")) Is To encompass the whole w/sheet. That works fine in the areas where time is to be entered but I lost my way somewhat and missed the fact that I need to have other columns with a numeric (no decimal) entry. I have tried to specify the cols where time is entered but to no avail. They are H,K,N,Q. Numeric entries are in D,G,J,M,P & S. This is all new to me and Id like some more assistance please -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input in w/sheet #2
Thankyou gentlemen. That was a great help.
There's just one thing (isn't there always?) Column E is performing as if it were formatted to give date and time e.g 25/01/1900 00:00:00 whenever I input my test time of 25 for 25minutes and which in other columns shows as 0.25 as designed. That's what shows(25/01/1900 00:00:00 0) in the formula box but in the cell it shows 00.00. I'm sure the problem lies with my original attempts to get this working. I have cleared data validation applied previously and formatted cells in every way I can think. The other cells are formatted Time 13.30 English US. Any ideas guys? -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Dave Peterson" wrote: Another one: If Application.Intersect(Target, me.Range("H:H,K:K,N:N,Q:Q")) Is Nothing Then Russell Dawson wrote: I had a problem which was resolved (or so I thought) yesterday regarding input of time. I was kindly given the solution Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) 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 I changed If Application.Intersect(Target, Range("A1:A10")) Is To If Application.Intersect(Target, Range("a:t")) Is To encompass the whole w/sheet. That works fine in the areas where time is to be entered but I lost my way somewhat and missed the fact that I need to have other columns with a numeric (no decimal) entry. I have tried to specify the cols where time is entered but to no avail. They are H,K,N,Q. Numeric entries are in D,G,J,M,P & S. This is all new to me and I€„¢d like some more assistance please -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. -- Dave Peterson . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input in w/sheet #2
Got it. I'd not even mentioned column E in my original post.
Thanks again. -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Russell Dawson" wrote: Thankyou gentlemen. That was a great help. There's just one thing (isn't there always?) Column E is performing as if it were formatted to give date and time e.g 25/01/1900 00:00:00 whenever I input my test time of 25 for 25minutes and which in other columns shows as 0.25 as designed. That's what shows(25/01/1900 00:00:00 0) in the formula box but in the cell it shows 00.00. I'm sure the problem lies with my original attempts to get this working. I have cleared data validation applied previously and formatted cells in every way I can think. The other cells are formatted Time 13.30 English US. Any ideas guys? -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Dave Peterson" wrote: Another one: If Application.Intersect(Target, me.Range("H:H,K:K,N:N,Q:Q")) Is Nothing Then Russell Dawson wrote: I had a problem which was resolved (or so I thought) yesterday regarding input of time. I was kindly given the solution Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) 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 I changed If Application.Intersect(Target, Range("A1:A10")) Is To If Application.Intersect(Target, Range("a:t")) Is To encompass the whole w/sheet. That works fine in the areas where time is to be entered but I lost my way somewhat and missed the fact that I need to have other columns with a numeric (no decimal) entry. I have tried to specify the cols where time is entered but to no avail. They are H,K,N,Q. Numeric entries are in D,G,J,M,P & S. This is all new to me and I€„¢d like some more assistance please -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. -- Dave Peterson . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| input # on sheet 1, pulls all info for # from sheet 2 | Setting up and Configuration of Excel | |||
| How do I change . to : when input in a time sheet by error | New Users to Excel | |||
| I want data on sheet 1 to input into sheet 2 also | Excel Worksheet Functions | |||
| Need Time Sheet that calculates hours/mins from input of times | Excel Worksheet Functions | |||
| can you input time (hh:mm:ss) without having to input the colon i. | Excel Discussion (Misc queries) | |||
![Russell Dawson[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
