Can these 2 VBA's be combined

[Toonies]

Hi and thanks for looking at my question,

I have found the following 2 VBA's and looking to combine them however my knowledge with vba is limited

here is the 1st VBA

 Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStrText As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("C9:C39, D9:D39, I9:I39, J9:J39, O9:O39, P9:P39, U9:U39, V9:V39, AA9:AA39, AB9:AB39, AG9:AG39, AH9:AH39, AM9:AM39, N9:AN39")) 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
            TimeStrTimeStrText = "00:0" & .Value
        Case 2 ' e.g., 12 = 00:12 AM
            TimeStrTimeStrText = "00:" & .Value
        Case 3 ' e.g., 735 = 7:35 AM
            TimeStrTimeStrText = Left(.Value, 1) & ":" & _
            Right(.Value, 2)
        Case 4 ' e.g., 1234 = 12:34
            TimeStrTimeStrText = Left(.Value, 2) & ":" & _
            Right(.Value, 2)
        Case Else
            Err.Raise 0
    End Select
    .Value = TimeValue(TimeStrTimeStrText)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "Are you sure you want to enter this"
Application.EnableEvents = True
End Sub

here is the 2nd VBA

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 
Dim DateStr As String
 
On Error GoTo EndMacro
If Application.Intersect(Target, Range("AM2")) 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(.Formula)
        Case 4 ' e.g., 9298 = 2-Sep-1998
            DateStr = Left(.Formula, 1) & "/" & _ 
            Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
        Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
            DateStr = Left(.Formula, 1) & "/" & _ 
                Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
        Case 6 ' e.g., 090298 = 2-Sep-1998
            DateStr = Left(.Formula, 2) & "/" & _ 
                Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
        Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
            DateStr = Left(.Formula, 1) & "/" & _ 
                Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
        Case 8 ' e.g., 09021998 = 2-Sep-1998
            DateStr = Left(.Formula, 2) & "/" & _ 
                Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
        Case Else
            Err.Raise 0
    End Select
    .Formula = DateValue(DateStr)
End If
 
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid date."
Application.EnableEvents = True
End Sub

Both original codes are found at

http://www.cpearson.com/excel/datetimeentry.htm

I look forward to any replies

Many thanks

Toonies

 

[Gerald Higgins]

Can they be combined ?
The short answer is yes, just copy and paste one into the other.

But perhaps more usefully, can YOU tell us what they actually do, and what you want to do ?

 

[MisterExcellent]

Hello Toonies,

you could combine the both codes as shown:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStrText As String
Dim DateStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("C9:C39, D9:D39, I9:I39, J9:J39, O9:O39, P9:P39, U9:U39, V9:V39, AA9:AA39, AB9:AB39, AG9:AG39, AH9:AH39, AM9:AM39, N9:AN39")) 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
            TimeStrText = "00:0" & .Value
        Case 2 ' e.g., 12 = 00:12 AM
            TimeStrText = "00:" & .Value
        Case 3 ' e.g., 735 = 7:35 AM
            TimeStrText = Left(.Value, 1) & ":" & _
            Right(.Value, 2)
        Case 4 ' e.g., 1234 = 12:34
            TimeStrText = Left(.Value, 2) & ":" & _
            Right(.Value, 2)
        Case Else
            Err.Raise 0
    End Select
    .Value = TimeValue(TimeStrText)
End If
End With
Application.EnableEvents = True
Exit Sub

If Application.Intersect(Target, Range("AM2")) 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(.Formula)
        Case 4 ' e.g., 9298 = 2-Sep-1998
            DateStr = Left(.Formula, 1) & "/" & _
            Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
        Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
            DateStr = Left(.Formula, 1) & "/" & _
                Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
        Case 6 ' e.g., 090298 = 2-Sep-1998
            DateStr = Left(.Formula, 2) & "/" & _
                Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
        Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
            DateStr = Left(.Formula, 1) & "/" & _
                Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
        Case 8 ' e.g., 09021998 = 2-Sep-1998
            DateStr = Left(.Formula, 2) & "/" & _
                Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
        Case Else
            Err.Raise 0
    End Select
    .Formula = DateValue(DateStr)
End If
 
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid date."
Application.EnableEvents = True
End Sub