Link to home
Start Free TrialLog in
Avatar of GISCOOBY
GISCOOBYFlag for United States of America

asked on

Excel VBA Worksheet_Change Question

Excel 2010 VBA
I am working with the Sub Worksheet_Change routine that takes an entry such as 824 and simply formats that entry to 8:24 (for time). My problem is that my routine actually runs twice. Once to set up the change and then again after the change is implemented. When certain times (such as 824) are entered an unexpected result (0.:35) is created the second run through the procedure. I modified the code to correct for this by changing the range value, but if I select and delete the values in more than one cell, Excel crashes. The original code does not crash if I delete multiple cells values. Any suggestions on how to get the best of both worlds?
Original Code:
Private Sub Worksheet_Change(ByVal target As Range)
    If target.Row = 65 _
    Or target.Row = 66 Then
       timeval = target.Value
       If InStr(1, timeval, ":") = 0 Then
        Select Case Len(timeval)
            Case 3
                valHour = Left(timeval, 1)
                valMinute = Right(timeval, 2)
                target.Value = valHour & ":" & valMinute
            Case 4
                valHour = Left(timeval, 2)
                valMinute = Right(timeval, 2)
                target.Value = valHour & ":" & valMinute
        End Select
       End If
    End If
End Sub

Modified Code:
Private Sub Worksheet_Change(ByVal target As Range)
    If target.Row = 65 _
    Or target.Row = 66 Then
       timeval = target.Value
      Rng = target.Address
     strWkst = Me.Name
       If InStr(1, timeval, ":") = 0 Then
        Select Case Len(timeval)
            Case 3
                valHour = Left(timeval, 1)
                valMinute = Right(timeval, 2)
                Worksheets(strWkst).Range(Rng).Value = valHour & ":" & valMinute
            Case 4824
                valHour = Left(timeval, 2)
                valMinute = Right(timeval, 2)
                Worksheets(strWkst).Range(Rng).Value = valHour & ":" & valMinute
        End Select
       End If
    End If
End Sub

ASKER CERTIFIED SOLUTION
Avatar of kgerb
kgerb
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GISCOOBY

ASKER

Rock on! Thank you! That was exactly what I was looking for.
You're welcome:).  Glad to help.