Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time entry with VBA
Hi all,
I've never bothered with VBA, simply because I haven't had a need. I've always been able to do anything I need with worksheet functions (and the groups help of course). Now I'm developing a sheet where it will come in very handy. Chip Pearson's Time entry worksheet change event fits the bill very nicely. (I'll post it at the end of this thread) All I need is to input time values as follows 123 converts to 1:23 445 converts to 4:45 12 converts to 0:12 etc. Chip's code works great for the range A1:A10. All I need to do is expand on this so that it works on multiple ranges and individual cells Say for example; A1:A10 C1:C12 F4:H20 A15:A17 B22:C26 G25 D34 F34 H34 etc. How do I go about adapting the code to work in these multiple ranges only. Thanks to all Martin Here is Chips code. 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time entry with VBA
one way:
Create a named range with all your sub-ranges included: "MyRange" and change code: If Application.Intersect(Target, Range("MyRange")) Is Nothing Then Untested! HTH "MartinW" wrote: Hi all, I've never bothered with VBA, simply because I haven't had a need. I've always been able to do anything I need with worksheet functions (and the groups help of course). Now I'm developing a sheet where it will come in very handy. Chip Pearson's Time entry worksheet change event fits the bill very nicely. (I'll post it at the end of this thread) All I need is to input time values as follows 123 converts to 1:23 445 converts to 4:45 12 converts to 0:12 etc. Chip's code works great for the range A1:A10. All I need to do is expand on this so that it works on multiple ranges and individual cells Say for example; A1:A10 C1:C12 F4:H20 A15:A17 B22:C26 G25 D34 F34 H34 etc. How do I go about adapting the code to work in these multiple ranges only. Thanks to all Martin Here is Chips code. 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time entry with VBA
Some code posted earlier (from Hesham ELhadad )on a similar question about
non-contiguous ranges .. might be useful: You can divide the Range as you need as follows: Sub CreateMyRange() Dim MyRange As Range Set MyRange = Application.Union(Range("A1:A24"), Range("A26:A47"), Range("A49:A100")) MyRange.Select ' Just to confirm the defined range. you can remove this line later MyRange.Name = "MyName" End Sub Hesham ELhadad "Toppers" wrote: one way: Create a named range with all your sub-ranges included: "MyRange" and change code: If Application.Intersect(Target, Range("MyRange")) Is Nothing Then Untested! HTH "MartinW" wrote: Hi all, I've never bothered with VBA, simply because I haven't had a need. I've always been able to do anything I need with worksheet functions (and the groups help of course). Now I'm developing a sheet where it will come in very handy. Chip Pearson's Time entry worksheet change event fits the bill very nicely. (I'll post it at the end of this thread) All I need is to input time values as follows 123 converts to 1:23 445 converts to 4:45 12 converts to 0:12 etc. Chip's code works great for the range A1:A10. All I need to do is expand on this so that it works on multiple ranges and individual cells Say for example; A1:A10 C1:C12 F4:H20 A15:A17 B22:C26 G25 D34 F34 H34 etc. How do I go about adapting the code to work in these multiple ranges only. Thanks to all Martin Here is Chips code. 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time entry with VBA
Thanks Toppers,
Your first solution was all that was needed. Amazed I didn't think of that as I use it in all sorts of situations in other worksheets. I guess my mind just goes blank at the mention of VBA. Some sort of psychological disfunction or something. <g Thanks Martin "Toppers" wrote in message ... Some code posted earlier (from Hesham ELhadad )on a similar question about non-contiguous ranges .. might be useful: You can divide the Range as you need as follows: Sub CreateMyRange() Dim MyRange As Range Set MyRange = Application.Union(Range("A1:A24"), Range("A26:A47"), Range("A49:A100")) MyRange.Select ' Just to confirm the defined range. you can remove this line later MyRange.Name = "MyName" End Sub Hesham ELhadad "Toppers" wrote: one way: Create a named range with all your sub-ranges included: "MyRange" and change code: If Application.Intersect(Target, Range("MyRange")) Is Nothing Then Untested! HTH "MartinW" wrote: Hi all, I've never bothered with VBA, simply because I haven't had a need. I've always been able to do anything I need with worksheet functions (and the groups help of course). Now I'm developing a sheet where it will come in very handy. Chip Pearson's Time entry worksheet change event fits the bill very nicely. (I'll post it at the end of this thread) All I need is to input time values as follows 123 converts to 1:23 445 converts to 4:45 12 converts to 0:12 etc. Chip's code works great for the range A1:A10. All I need to do is expand on this so that it works on multiple ranges and individual cells Say for example; A1:A10 C1:C12 F4:H20 A15:A17 B22:C26 G25 D34 F34 H34 etc. How do I go about adapting the code to work in these multiple ranges only. Thanks to all Martin Here is Chips code. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Entry | Excel Discussion (Misc queries) | |||
Easy Time Entry | New Users to Excel | |||
Time Entry | New Users to Excel | |||
Time entry | Excel Discussion (Misc queries) | |||
Time Entry | Excel Worksheet Functions |