| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using code to automate "h:mm"
I am using a vb code that when entering hours and minutes it adds colon
between the hours and minutes. ie if I enter 105 it gives 1:05. The code is working find for some cells, but every now and then it throws a track! For instance the above example it gives me 2520:00 instead of 1:05. Any ideas????? -- hol |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using code to automate "h:mm"
Post your code, and someone will be able to help.
Pete On Nov 30, 10:19 am, hol wrote: I am using a vb code that when entering hours and minutes it adds colon between the hours and minutes. ie if I enter 105 it gives 1:05. The code is working find for some cells, but every now and then it throws a track! For instance the above example it gives me 2520:00 instead of 1:05. Any ideas????? -- hol |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using code to automate "h:mm"
This is the code I am using:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String Application.EnableEvents = False On Error GoTo EndMacro If Application.Intersect(Target, Range("D4:H5000")) 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 With Target If .HasFormula = False Then Select Case Len(.Formula) Case 1 'e.g., 1=00:01 TimeStr = "0:0" & .Value Case 2 'e.g., 12=001.2 TimeStr = "00:" & Right(.Value, 2) Case 3 'e.g., 735=73.5 TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2) Case 4 'e.g., 1234=123.4 TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2) Case 5 'e.g., 12345 = 1234.5 NOT 12:03:45 TimeStr = Left(.Value, 3) & ":" & Right(.Value, 2) Case 6 'e.g., 123456 = 1234.5 NOT 12:03:45 TimeStr = Left(.Value, 4) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select ..Value = TimeValue(TimeStr) End If End With Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- hol "Pete_UK" wrote: Post your code, and someone will be able to help. Pete On Nov 30, 10:19 am, hol wrote: I am using a vb code that when entering hours and minutes it adds colon between the hours and minutes. ie if I enter 105 it gives 1:05. The code is working find for some cells, but every now and then it throws a track! For instance the above example it gives me 2520:00 instead of 1:05. Any ideas????? -- hol |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using code to automate "h:mm"
I am using a userform with textbox to insert times on to the worksheet. I
think the problem lies with that? -- hol "hol" wrote: This is the code I am using: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String Application.EnableEvents = False On Error GoTo EndMacro If Application.Intersect(Target, Range("D4:H5000")) 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 With Target If .HasFormula = False Then Select Case Len(.Formula) Case 1 'e.g., 1=00:01 TimeStr = "0:0" & .Value Case 2 'e.g., 12=001.2 TimeStr = "00:" & Right(.Value, 2) Case 3 'e.g., 735=73.5 TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2) Case 4 'e.g., 1234=123.4 TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2) Case 5 'e.g., 12345 = 1234.5 NOT 12:03:45 TimeStr = Left(.Value, 3) & ":" & Right(.Value, 2) Case 6 'e.g., 123456 = 1234.5 NOT 12:03:45 TimeStr = Left(.Value, 4) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- hol "Pete_UK" wrote: Post your code, and someone will be able to help. Pete On Nov 30, 10:19 am, hol wrote: I am using a vb code that when entering hours and minutes it adds colon between the hours and minutes. ie if I enter 105 it gives 1:05. The code is working find for some cells, but every now and then it throws a track! For instance the above example it gives me 2520:00 instead of 1:05. Any ideas????? -- hol |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using code to automate "h:mm"
A few things.
You say you want hh:mm yet you hace cases of 5 & 6 digit inputs, what are these for? In my response they are deleted The single digit inpot is formatted incorrectly if you want 00:01 your msgbox was in the wrong place The code accepted an input of 199 which when formatted as 1:99 gives an invalid time so you must trap for this. You should disable alerts for the minimum time required:- Have a look at this Its change to a singele cell a1 for debugging so change this back Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("a1")) 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 With Target If .HasFormula = False Then Select Case Len(Target.Value) Case 1 'e.g., 1=00:01 TimeStr = "00:0" & Target.Value Case 2 'e.g., 12=001.2 If Target.Value 59 Then GoTo EndMacro TimeStr = "00:" & Right(Target.Value, 2) Case 3 'e.g., 735=73.5 If Right(Target.Value, 2) 59 Then GoTo EndMacro TimeStr = Left(.Value, 1) & ":" & Right(Target.Value, 2) Case 4 'e.g., 1234=123.4 If Right(Target.Value, 2) 59 Then GoTo EndMacro TimeStr = Left(.Value, 2) & ":" & Right(Target.Value, 2) Case Else EndMacro: MsgBox "You did not enter a valid time" TimeStr = "00:00" End Select Application.EnableEvents = False Target.Value = TimeStr Application.EnableEvents = True End If End With Exit Sub Mike "hol" wrote: This is the code I am using: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String Application.EnableEvents = False On Error GoTo EndMacro If Application.Intersect(Target, Range("D4:H5000")) 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 With Target If .HasFormula = False Then Select Case Len(.Formula) Case 1 'e.g., 1=00:01 TimeStr = "0:0" & .Value Case 2 'e.g., 12=001.2 TimeStr = "00:" & Right(.Value, 2) Case 3 'e.g., 735=73.5 TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2) Case 4 'e.g., 1234=123.4 TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2) Case 5 'e.g., 12345 = 1234.5 NOT 12:03:45 TimeStr = Left(.Value, 3) & ":" & Right(.Value, 2) Case 6 'e.g., 123456 = 1234.5 NOT 12:03:45 TimeStr = Left(.Value, 4) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- hol "Pete_UK" wrote: Post your code, and someone will be able to help. Pete On Nov 30, 10:19 am, hol wrote: I am using a vb code that when entering hours and minutes it adds colon between the hours and minutes. ie if I enter 105 it gives 1:05. The code is working find for some cells, but every now and then it throws a track! For instance the above example it gives me 2520:00 instead of 1:05. Any ideas????? -- hol |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using code to automate "h:mm"
Thanks Mike,
The worksheet keeps a tally of hours. So one day I could enter 2hours and 30minutes (2:30) it could be more or less, depending on the work is done for that day. I have a summary sheet that keeps a running total of the hours. I have changed the way that the hours are inserted. I used to insert it straight on the sheet, but now I have a userform which I insert the hours and or minutes and that in turn inserts it in the worksheet. Maybe that is the problem.....i dont know! -- hol "Mike H" wrote: A few things. You say you want hh:mm yet you hace cases of 5 & 6 digit inputs, what are these for? In my response they are deleted The single digit inpot is formatted incorrectly if you want 00:01 your msgbox was in the wrong place The code accepted an input of 199 which when formatted as 1:99 gives an invalid time so you must trap for this. You should disable alerts for the minimum time required:- Have a look at this Its change to a singele cell a1 for debugging so change this back Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("a1")) 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 With Target If .HasFormula = False Then Select Case Len(Target.Value) Case 1 'e.g., 1=00:01 TimeStr = "00:0" & Target.Value Case 2 'e.g., 12=001.2 If Target.Value 59 Then GoTo EndMacro TimeStr = "00:" & Right(Target.Value, 2) Case 3 'e.g., 735=73.5 If Right(Target.Value, 2) 59 Then GoTo EndMacro TimeStr = Left(.Value, 1) & ":" & Right(Target.Value, 2) Case 4 'e.g., 1234=123.4 If Right(Target.Value, 2) 59 Then GoTo EndMacro TimeStr = Left(.Value, 2) & ":" & Right(Target.Value, 2) Case Else EndMacro: MsgBox "You did not enter a valid time" TimeStr = "00:00" End Select Application.EnableEvents = False Target.Value = TimeStr Application.EnableEvents = True End If End With Exit Sub Mike "hol" wrote: This is the code I am using: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String Application.EnableEvents = False On Error GoTo EndMacro If Application.Intersect(Target, Range("D4:H5000")) 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 With Target If .HasFormula = False Then Select Case Len(.Formula) Case 1 'e.g., 1=00:01 TimeStr = "0:0" & .Value Case 2 'e.g., 12=001.2 TimeStr = "00:" & Right(.Value, 2) Case 3 'e.g., 735=73.5 TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2) Case 4 'e.g., 1234=123.4 TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2) Case 5 'e.g., 12345 = 1234.5 NOT 12:03:45 TimeStr = Left(.Value, 3) & ":" & Right(.Value, 2) Case 6 'e.g., 123456 = 1234.5 NOT 12:03:45 TimeStr = Left(.Value, 4) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- hol "Pete_UK" wrote: Post your code, and someone will be able to help. Pete On Nov 30, 10:19 am, hol wrote: I am using a vb code that when entering hours and minutes it adds colon between the hours and minutes. ie if I enter 105 it gives 1:05. The code is working find for some cells, but every now and then it throws a track! For instance the above example it gives me 2520:00 instead of 1:05. Any ideas????? -- hol |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using code to automate "h:mm"
Do I need to format the textbox for time?
-- hol "hol" wrote: I am using a vb code that when entering hours and minutes it adds colon between the hours and minutes. ie if I enter 105 it gives 1:05. The code is working find for some cells, but every now and then it throws a track! For instance the above example it gives me 2520:00 instead of 1:05. Any ideas????? -- hol |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Linear Mode
