Worksheet change event applying to multiple ranges

Hello - there many quesitons that very nearly provide the answer to this, and think I've tried them all....I'm probably missing something as I'm putting the pieces together. The following code works just find on a range in an excel table I have (adding colon's to times entered), but I have 5 seperate tables, that I need this to apply to. I have tried writing 5 sub procedures and calling them based on condition, but thats not working - probably my syntax.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim TimeStr As String

    If Application.Intersect(Target, Range("TableResp[Time]")) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If IsDate(Target.Text) And Target.Value < 1 Then Exit Sub

    On Error GoTo EndMacro

    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"
    Target.Value = ""
    Application.EnableEvents = True
End Sub

thanks

Hy,

The Error isn't she here ?

If Application.Intersect(Target, Range("TableResp[Time]")) Is Nothing Then Exit Sub

The rest of the code seems to work well

I don't get an error at all with that particular code actually. What i need is for this same operation to apply to 4 other ranges in the worksheet, in addition to Range("TableResp[Time]"):

I also need it to apply to Range("TableCVS[Time]), Range ("TableRen[Time]"), and 3 other tables.

A little awkward, but did you try repeating the code for each range?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TimeStr As String

    If Not Intersect(Target, Range("TableResp[Time]")) Is Nothing Then
    If Target.Cells.count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If IsDate(Target.Text) And Target.Value < 1 Then Exit Sub

    On Error GoTo EndMacro

    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"
    Target.Value = ""
    Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("TableCVS[Time]")) Is Nothing Then
    If Target.Cells.count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If IsDate(Target.Text) And Target.Value < 1 Then Exit Sub

    On Error GoTo EndMacro

    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"
    Target.Value = ""
    Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("TableRen[Time]")) Is Nothing Then
    If Target.Cells.count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If IsDate(Target.Text) And Target.Value < 1 Then Exit Sub

    On Error GoTo EndMacro

    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"
    Target.Value = ""
    Application.EnableEvents = True
    End If
' AND SO ON, AND SO ON
End Sub

You just create 5 other test as same the first

Hi,
Oh john, there are easier, no

It's the same think for the 5 or more area, so the code below should work

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim TimeStr As String
  ' Test first multi-selection or deleted value
  If Target.Cells.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If IsDate(Target.Text) And Target.Value < 1 Then Exit Sub
  ' Then test the location of change
  If Not Application.Intersect(Target, Range("TableResp[Time]")) Is Nothing _
     Or Not Application.Intersect(Target, Range("TableCVS[Time]")) Is Nothing _
     Or Not Application.Intersect(Target, Range("TableRen[Time]")) Is Nothing Then

    On Error GoTo EndMacro
    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
EndMacro:
    If Err.Number <> 0 Then
      MsgBox "You did not enter a valid time"
      Target.Value = ""
    End If
    Application.EnableEvents = True
  End If
End Sub

Regards

I should add, my problem with calling this (sorry if my terminology isn't correct), has to do with the arguments. When I try to make Brians suggestion a sub procedure

Private Sub Worksheet_Change(ByVal Target As Range)
Call BriansCode
End Sub

Sub BriansCode(ByValTarget As Range)
'Brians Code

This I can't get working. Obviously I'm asking questions that are beyond my abilities so sorry if they aren't phrased exactly right. doing my best to describe what I hope to accomplish.

Thanks both for the suggestions.

John I tried your suggestion over and over and couldn't get it to work. there's something with this procedure that results in entering a time 1559 that results in "00:00", and it has something to do with the application.enableevents. With help on this board I got it working with this single procedure but when I tried your suggestion, that issue reappeared. Maybe because there were 10 of these using this method.

Brian - your suggestion seems to have solved this. Presumably because it kept the same format and there remains only two of those enableevent statements even though it applies to 5 tables. Thanks!! So that solved the problem! With John's suggestion there was 10 of these statements and i'm guessing that's why the problem reared it's ugly head. I noticed other people having this same issue when I was looking myself.

I suppose why I'm looking have this as a called procedure depending on what column you happen to be in is because I would eventually like to have similar code that acts on a date column within the same table. So if you enter a date as 102513, it's formatted as 25/Oct/13. If you're in the "time" column, the procedure that Brian recommended is called.

Why do you want "export" the code in a other Sub ?

See attached worksheet....maybe I'm just not looking at this the correct way. But I intend to do the same sort of formatting with the date field. this would require almost identical code (i presume). I thought it would be easier to have two seperate sub procedures: yours formatting the time, and another formatting the date. And the worksheet change event "calling" the appropriate one based on whether or not a date field was changed, or a time field was changed.

Hi,

A If ... End If it used for one condition, you can Add more If... Endif

Try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim TimeStr As String
  ' Test first multi-selection or deleted value
  If Target.Cells.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If IsDate(Target.Text) And Target.Value < 1 Then Exit Sub
  ' Then test the location of change
  ' TEST FOR RANGES of TIME
  If Not Application.Intersect(Target, Range("Table1[Time]")) Is Nothing _
     Or Not Application.Intersect(Target, Range("Table2[Time]")) Is Nothing _
     Or Not Application.Intersect(Target, Range("Table3[Time]")) Is Nothing Then

    On Error GoTo EndMacro
    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
EndMacro:
    If Err.Number <> 0 Then
      MsgBox "You did not enter a valid time"
      Target.Value = ""
    End If
    Application.EnableEvents = True
  End If
  ' TEST FOR RANGES of DATE
  If Not Application.Intersect(Target, Range("Table1[Date]")) Is Nothing _
     Or Not Application.Intersect(Target, Range("Table2[Date]")) Is Nothing Then
     MsgBox "It's a range of date"
  End If
End Sub

Thanks - you make it sound and look so easy so I tried to use this and apply the time formatting to the date column as well (just to test). Had two questions come up as I was doing this:

1) Is the error handling as I have it below OK? I enter in an invalid time in teh date field and no error pops up.
2) The application.enable events. Does each if...endif start with enableevents = false, and end with enableevents = true?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim TimeStr As String
  Dim DateStr As String
  ' Test first multi-selection or deleted value
  If Target.Cells.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If IsDate(Target.Text) And Target.Value < 1 Then Exit Sub
  ' Then test the location of change
  ' TEST FOR RANGES of TIME
  If Not Application.Intersect(Target, Range("Table1[Time]")) Is Nothing _
     Or Not Application.Intersect(Target, Range("Table2[Time]")) Is Nothing _
     Or Not Application.Intersect(Target, Range("Table3[Time]")) Is Nothing Then

    On Error GoTo EndTime
    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
EndTime:
    If Err.Number <> 0 Then
      MsgBox "You did not enter a valid time"
      Target.Value = ""
    End If
    Application.EnableEvents = True
  End If
  ' TEST FOR RANGES of DATE
  
  If Not Application.Intersect(Target, Range("Table1[Date]")) Is Nothing _
     Or Not Application.Intersect(Target, Range("Table2[Date]")) Is Nothing _
     Or Not Application.Intersect(Target, Range("Table3[Date]")) Is Nothing Then

    On Error GoTo EndDate
    Application.EnableEvents = False
    With Target
      If .HasFormula = False Then
        Select Case Len(.Value)
        Case 1    ' e.g., 1 = 00:01 AM
          DateStr = "00:0" & .Value
        Case 2    ' e.g., 12 = 00:12 AM
          DateStr = "00:" & .Value
        Case 3    ' e.g., 735 = 7:35 AM
          DateStr = Left(.Value, 1) & ":" & _
                    Right(.Value, 2)
        Case 4    ' e.g., 1234 = 12:34
          DateStr = Left(.Value, 2) & ":" & _
                    Right(.Value, 2)
        Case 5    ' e.g., 12345 = 1:23:45 NOT 12:03:45
          DateStr = Left(.Value, 1) & ":" & _
                    Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
        Case 6    ' e.g., 123456 = 12:34:56
          DateStr = Left(.Value, 2) & ":" & _
                    Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
        Case Else
          Err.Raise 0
        End Select
        .Value = TimeValue(DateStr)
      End If
    End With
EndDate:
    If Err.Number <> 0 Then
      MsgBox "You did not enter a valid date"
      Target.Value = ""
    End If
    Application.EnableEvents = True
  End If
End Sub

Really apprecaite your help, if you have the patience to continue to respond, I won't be able to reply for some time. Off to catch a flight..