GISCOOBY
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
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(
Case 4824
valHour = Left(timeval, 2)
valMinute = Right(timeval, 2)
Worksheets(strWkst).Range(
End Select
End If
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome:). Glad to help.
ASKER