| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Two Macros-One Module
Your Team answered two questions with copy-able codes that I was able to get
to work seperately. (1) Entering time without ":" or "AM/PM" and (2) a default to "PM" only upon entry/exit of cell - they're great! I never did a macro before and it is cool! Follwoing are the two macro codes so that you may tell me which lines to delete/change. MACRO-1 - - - - - - - - - - - - - - - Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("E8:F13,E18:F23")) 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 - - - - - - - - - - - - - - - - - - MACRO-2 - - - - - - - - - - On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("F8:F13,F18:F23")) Is Nothing Then With Target If .Value < 1 Then If .Value < 0.5 Then .Value = 0.5 + .Value End If End If End With End If ws_exit: Application.EnableEvents = True End Sub - - - - - - - - - - - - - - - - - - - I'm so close to getting this to work - Can you help? BULAMAN |
|
#2
|
|||
|
|||
|
Hi
not tested but try: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("E8:F13,E18:F23")) 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 If .Value < 1 Then If .Value < 0.5 Then .Value = 0.5 + .Value End If Application.EnableEvents = true exit sub End If 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 "BulaMan" wrote: Your Team answered two questions with copy-able codes that I was able to get to work seperately. (1) Entering time without ":" or "AM/PM" and (2) a default to "PM" only upon entry/exit of cell - they're great! I never did a macro before and it is cool! |
|
#3
|
|||
|
|||
|
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("E8:F13,E18:F23")) 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) If .Value < 0.5 Then .Value = 0.5 + .Value End If End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "BulaMan" wrote in message ... Your Team answered two questions with copy-able codes that I was able to get to work seperately. (1) Entering time without ":" or "AM/PM" and (2) a default to "PM" only upon entry/exit of cell - they're great! I never did a macro before and it is cool! Follwoing are the two macro codes so that you may tell me which lines to delete/change. MACRO-1 - - - - - - - - - - - - - - - Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("E8:F13,E18:F23")) 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 - - - - - - - - - - - - - - - - - - MACRO-2 - - - - - - - - - - On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("F8:F13,F18:F23")) Is Nothing Then With Target If .Value < 1 Then If .Value < 0.5 Then .Value = 0.5 + .Value End If End If End With End If ws_exit: Application.EnableEvents = True End Sub - - - - - - - - - - - - - - - - - - - I'm so close to getting this to work - Can you help? BULAMAN |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macros & pie charts | Charts and Charting in Excel | |||
| Macros for playing sounds | Excel Discussion (Misc queries) | |||
| How do I copy excel macros from one pc to another? | Excel Discussion (Misc queries) | |||
| How long have macros been available in Excel? | Excel Discussion (Misc queries) | |||
| How do you create a macros without overwriting the previous one y. | Excel Discussion (Misc queries) | |||
Linear Mode
