By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,058 Members | 1,336 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,058 IT Pros & Developers. It's quick & easy.

VB in Excel

P: 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
Share this Question
Share on Google+
7 Replies


100+
P: 1,646
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

Expert 5K+
P: 8,434
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

P: 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

100+
P: 1,646
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

Expert 5K+
P: 8,434
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

100+
P: 1,646
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

P: 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.