VB in Excel

[Batron]

Hello All
I am very new to this VB in excel and eager to learn as much as possible about it. I found this code online to help enter time in spreadsheets easier (without having to add the : between the hour and minutes. I tried to enter the code but it didn't work. Could someone check out what might be wrong with it? Thanks

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) 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:

[willakawill]

Hello All
I am very new to this VB in excel and eager to learn as much as possible about it. I found this code online to help enter time in spreadsheets easier (without having to add the : between the hour and minutes. I tried to enter the code but it didn't work. Could someone check out what might be wrong with it? Thanks

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) 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:

Hi. How did you implement this code? Did you pass a range of cells to it from another part of your code?

[Killer42]

Hi. How did you implement this code? Did you pass a range of cells to it from another part of your code?

Also, could you be a little more specific about what "doesn't work" means? And perhaps the details of how/where you entered the code? For example, if you just pasted this into a worksheet it would (presumably) do absolutely nothing.

[Batron]

Hi. How did you implement this code? Did you pass a range of cells to it from another part of your code?

Hi - I am not sure what you mean by "pass a range of cells to it". I went into MS Excel objects--clicked on my sheet--pasted it in the sheet code box. I do not know if my next step is run or not but that is what I did and an error came up - "compile error: expected End Sub. I am not sure if I am just supposed to paste it in there and close out and go to the worksheet or not. I do not what my next step would be after pasting the code. Once I paste it does the sheet automatically work as far as the code would say or do I need to highlight the cells I want it to work on and run a macro? Thank you for your assistance.

[willakawill]

Hi - I am not sure what you mean by "pass a range of cells to it". I went into MS Excel objects--clicked on my sheet--pasted it in the sheet code box. I do not know if my next step is run or not but that is what I did and an error came up - "compile error: expected End Sub. I am not sure if I am just supposed to paste it in there and close out and go to the worksheet or not. I do not what my next step would be after pasting the code. Once I paste it does the sheet automatically work as far as the code would say or do I need to highlight the cells I want it to work on and run a macro? Thank you for your assistance.

Try changing that last part
Exit Sub
EndMacro:

to
End Sub

[Killer42]

Try changing that last part
Exit Sub
EndMacro:
to
End Sub

Actually, I would have just added the End Sub onto the end. The EndMacro label is still needed for the On Error code. And even though in this case there won't be any code after the label, I think it's good general practice to have an Exit Sub before this sort of error-handler label.

So, my recommendation would be...

Exit Sub
 
EndMacro:
End Sub
 

[willakawill]

Actually, I would have just added the End Sub onto the end. The EndMacro label is still needed for the On Error code. And even though in this case there won't be any code after the label, I think it's good general practice to have an Exit Sub before this sort of error-handler label.

So, my recommendation would be...

Exit Sub
 
EndMacro:
End Sub
 

Nice catch again K. I missed that completely.

[Batron]

That worked!!! Thanks everyone