Automatically inserting Colon (:) in multiple columns under the Options Explicit-CodePudding

and thank you for checking this issue out.

Still a newbie. I was looking for a code to automatically insert the ':' (Colon) into the columns R and S, W and X, and found a code that I thought I could customise to my needs, but I am facing 2 issues:

The code works in R and S, but also need the code to run in columns W and X as well
I get an Error: Variable not Defined - stopping at TLen and I guess it will also stop at TimeV

The programmer doesn't use the Option Explicit, (works ok when no Option Explicit). But all my codes are always with Option Explicit, but I'm not sure how to write the Dim for the two variables.

This code is in a specific worksheet, in the Worksheet_Change sub, where I have other code for other things, like the time stamp when people make a selection from column B, it will automatically populate when a selection is made in Column B.

I have tried the Colon code in another workbook, without the Option Explicit and it works without giving errors.

The source of the code came from

https://www.youtube.com/watch?v=ATxaNbTV2d0 (Excel is Fun - Excel VBA Tips n Tricks #12 NO MORE COLONS When Typing Time of Day

I've adapted the code to reference the columns R and S in the code below.

    Private Sub Worksheet_Change(ByVal Target As Range)
    'This code will ADD the COLON for TIME automatically
'Code from: https://www.youtube.com/watch?v=ATxaNbTV2d0 (Excel is Fun -
'Excel VBA Tips n Tricks #12 NO MORE COLONS When Typing Time of Day, Type 123 instead of 01colon23 AM

'To avoid an error if you select more than 1 cell, this next line of code will exit the sub


    If Selection.Count > 1 Then
    Exit Sub
    End If
    
    If Not Intersect(Range("R4:S1200"), Target) Is Nothing Then
    TLen = Len(Target)
    [![Layout of Worksheet and sample of the columns that need automatic insertion of colons ][1]][1]
    If TLen = 1 Then
    TimeV = TimeValue(Target & ":00")
    
    ElseIf TLen = 2 Then
    TimeV = TimeValue(Target & ":00")
    
    ElseIf TLen = 3 Then
    TimeV = TimeValue(Left(Target, 1) & ":" & Right(Target, 2))
    
    ElseIf TLen = 4 Then
    TimeV = TimeValue(Left(Target, 2) & ":" & Right(Target, 2))
    
    ElseIf TLen > 4 Then
    'Do nothing
    
    End If
    
    ' Target.NumberFormat = "HH:MM"
    Application.EnableEvents = False
    
    Target = TimeV
    Application.EnableEvents = True
    
    End If
End Sub

I'm hoping that you will be able to help me ... hopefully I haven't confused you.

Thanking you in advance for any assistance,

Regards, TheShyButterfly

CodePudding user response：

Expand the range of the Intersect Intersect(Range("R:S,W:X"),Target).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If IsNumeric(Target) = False Then
        MsgBox Target & " is not a number", vbExclamation
        Exit Sub
    ElseIf Intersect(Range("R:S,W:X"), Target) Is Nothing Then
        Exit Sub
    End If
    
    Dim n As Long
    n = Len(Target)
    If n >= 1 And n <= 4 Then
        Application.EnableEvents = False
        Target.NumberFormat = "hh:mm"
        If n <= 2 Then
            Target.Value2 = TimeSerial(Target, 0, 0)
        Else
            Target.Value2 = TimeSerial(Int(Target / 100), Target Mod 100, 0)
        End If
        Application.EnableEvents = True
     End If
End Sub