Times

[fws1]

I am entering times into ms excel with the aid of VBA

my VBA string is:

Private Sub Worksheet_Change(ByVal Target As Range)
UserInput = Target.Value
If UserInput > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub

I have entered all of the times already and I am trying to copy paste the time values into the sheet that VBA is set for....but it won't let me.

It gives me an error of "Run-time error "13", type mismatch.

 

[Scott Huish]

You need to isolate the range you want to convert time values to. I also changed the way you did that:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("A:A"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    If IsNumeric(c) Then c = Format(c, "00\:00")
Next
Application.EnableEvents = True
End Sub

This will now only affect Column A.

 

[fws1]

Thanks a bunch!! I owe you a beer or 2.