Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time entry macro (entering times via the keypad)
Hello, I have the following VBA macro that allows a user to enter
times via the keypad (without having to enter the colon between hours and minutes or seconds). The macro transforms a 3 to 6 digit number into a time entry. It works great for the columns that I actually want to enter times into (columns F and G); however, if I need to change the data in any other column, that data also changes to a time (for example, Column D is group number, and when I enter, say, 3, I get an output of 12:03:00 AM). Is there a way in this macro (or another route) to limit a range of what can/should change? Thanks. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:AV10000")) 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.programming
|
|||
|
|||
Time entry macro (entering times via the keypad)
Try replacing A1:AV10000 with F1:G1000 -- skatonn ----------------------------------------------------------------------- skatonni's Profile: http://www.officehelp.in/member.php?userid=418 View this thread: http://www.officehelp.in/showthread.php?t=120490 Posted from - http://www.officehelp.i |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time entry macro (entering times via the keypad)
Change
If Application.Intersect(Target, Range("A1:AV10000")) Is Nothing Then to If Application.Intersect(Target, Range("F1:G10000")) Is Nothing Then -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Phrank" wrote in message ... Hello, I have the following VBA macro that allows a user to enter times via the keypad (without having to enter the colon between hours and minutes or seconds). The macro transforms a 3 to 6 digit number into a time entry. It works great for the columns that I actually want to enter times into (columns F and G); however, if I need to change the data in any other column, that data also changes to a time (for example, Column D is group number, and when I enter, say, 3, I get an output of 12:03:00 AM). Is there a way in this macro (or another route) to limit a range of what can/should change? Thanks. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:AV10000")) 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.programming
|
|||
|
|||
Time entry macro (entering times via the keypad)
Pretty simple. I thought for sure I had tried that, but I obviously
overlooked it. It works. Thanks to both that helped. Frank On Wed, 20 Sep 2006 11:36:45 +0100, "Bob Phillips" wrote: Change If Application.Intersect(Target, Range("A1:AV10000")) Is Nothing Then to If Application.Intersect(Target, Range("F1:G10000")) Is Nothing Then |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to enter time into cell using keypad only: MULTIPLE COLUMNS | Excel Discussion (Misc queries) | |||
Automatically entering time and date to an entry | Excel Discussion (Misc queries) | |||
Entering Time Macro | Excel Worksheet Functions | |||
enter a time into a cell, have the cell show two times the entry | Excel Worksheet Functions | |||
Data Entry With Drop down Keypad | Excel Discussion (Misc queries) |