468,301 Members | 1,532 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,301 developers. It's quick & easy.

VB in Excel

3
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:
Nov 9 '06 #1
7 3009
willakawill
1,646 1GB
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?
Nov 9 '06 #2
Killer42
8,434 Expert 8TB
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.
Nov 9 '06 #3
Batron
3
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.
Nov 10 '06 #4
willakawill
1,646 1GB
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
Nov 10 '06 #5
Killer42
8,434 Expert 8TB
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...
Expand|Select|Wrap|Line Numbers
  1. Exit Sub
  2.  
  3. EndMacro:
  4. End Sub
  5.  
Nov 11 '06 #6
willakawill
1,646 1GB
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...
Expand|Select|Wrap|Line Numbers
  1. Exit Sub
  2.  
  3. EndMacro:
  4. End Sub
  5.  
Nice catch again K. I missed that completely.
Nov 12 '06 #7
Batron
3
That worked!!! Thanks everyone
Nov 13 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by Allison Bailey | last post: by
6 posts views Thread by Matthew Wieder | last post: by
14 posts views Thread by pmud | last post: by
22 posts views Thread by Howard Kaikow | last post: by
7 posts views Thread by Alain \Mbuna\ | last post: by
9 posts views Thread by Doug Glancy | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.