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 |
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) |