VB in Excel

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Batron
    New Member
    • Nov 2006
    • 3
    #1

    VB in Excel

    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_Chang e(ByVal Target As Excel.Range)
    Dim TimeStr As String

    On Error GoTo EndMacro
    If Application.Int ersect(Target, Range("A1:A10") ) Is Nothing Then
    Exit Sub
    End If
    If Target.Cells.Co unt > 1 Then
    Exit Sub
    End If
    If Target.Value = "" Then
    Exit Sub
    End If

    Application.Ena bleEvents = 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(TimeS tr)
    End If
    End With
    Application.Ena bleEvents = True
    Exit Sub
    EndMacro:
    Tags: None
    • willakawill
      Top Contributor
      • Oct 2006
      • 1646
      #2
      Originally posted by 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_Chang e(ByVal Target As Excel.Range)
      Dim TimeStr As String

      On Error GoTo EndMacro
      If Application.Int ersect(Target, Range("A1:A10") ) Is Nothing Then
      Exit Sub
      End If
      If Target.Cells.Co unt > 1 Then
      Exit Sub
      End If
      If Target.Value = "" Then
      Exit Sub
      End If

      Application.Ena bleEvents = 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(TimeS tr)
      End If
      End With
      Application.Ena bleEvents = 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?

        Comment

        • Killer42
          Recognized Expert Expert
          • Oct 2006
          • 8429
          #3
          Originally posted by willakawill
          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.

            Comment

            • Batron
              New Member
              • Nov 2006
              • 3
              #4
              Originally posted by willakawill
              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.

                Comment

                • willakawill
                  Top Contributor
                  • Oct 2006
                  • 1646
                  #5
                  Originally posted by Batron
                  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

                    Comment

                    • Killer42
                      Recognized Expert Expert
                      • Oct 2006
                      • 8429
                      #6
                      Originally posted by willakawill
                      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...
                      Code:
                      Exit Sub

EndMacro:
End Sub

                        Comment

                        • willakawill
                          Top Contributor
                          • Oct 2006
                          • 1646
                          #7
                          Originally posted by Killer42
                          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...
                          Code:
                          Exit Sub

EndMacro:
End Sub
                          Nice catch again K. I missed that completely.

                            Comment

                            • Batron
                              New Member
                              • Nov 2006
                              • 3
                              #8
                              That worked!!! Thanks everyone

                                Comment

                                Previous template Next
                                Working...