Cell Formatting w/following Macro
 

I am using the following macro to speed the input of times into a ws,
the problem I have run across is the time go into every even numbered
row, and each work assigment goes in the odd row, when I enter the work
assigments in the odd rows I get a colon inserted in the text.

I have tried formatting the cells to text only but that doesn't work, I
have tried to just put in the even numbered cells i.e. B6:C6, B8:C8 etc
but I get a range is to long error in VBA,

Thanks for any help

Rick

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserInput, r&
Application.EnableEvents = False
If Not Intersect(Target, [B:C,E:F,H:I,K:L,N:O,Q:R,T:U]) Is Nothing
Then
If Not Selection.Cells.Count = 1 Then
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
If Target.Value < 1 Then
UserInput = Format(Int(Target.Value * 24) * 100 + ((Target.Value * 24)
- Int(Target.Value * 24)) * 60, "0000")
Else
UserInput = Format(Target.Value, "0000")
End If
If Len(UserInput) 1 Then
Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput,
2)
End If
r = Target.Row
If Cells(r, 9) = "" Or Cells(r, 10) = "" Then
Cells(r, 11).ClearContents
Else
Cells(r, 4).FormulaR1C1 = "=MOD(RC[-1]-RC[-2],1)"
End If
End If
Application.EnableEvents = True
End Sub


--
RudeRam


------------------------------------------------------------------------
RudeRam's Profile: http://www.excelforum.com/member.php...fo&userid=8818
View this thread: http://www.excelforum.com/showthread...hreadid=555575

Cell Formatting w/following Macro
 

Rick,

Use this as the first line of your macro:

If Target.Row Mod 2 = 1 Then Exit Sub

HTH,
Bernie
MS Excel MVP


"RudeRam" wrote in message
...

I am using the following macro to speed the input of times into a ws,
the problem I have run across is the time go into every even numbered
row, and each work assigment goes in the odd row, when I enter the work
assigments in the odd rows I get a colon inserted in the text.

I have tried formatting the cells to text only but that doesn't work, I
have tried to just put in the even numbered cells i.e. B6:C6, B8:C8 etc
but I get a range is to long error in VBA,

Thanks for any help

Rick

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserInput, r&
Application.EnableEvents = False
If Not Intersect(Target, [B:C,E:F,H:I,K:L,N:O,Q:R,T:U]) Is Nothing
Then
If Not Selection.Cells.Count = 1 Then
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
If Target.Value < 1 Then
UserInput = Format(Int(Target.Value * 24) * 100 + ((Target.Value * 24)
- Int(Target.Value * 24)) * 60, "0000")
Else
UserInput = Format(Target.Value, "0000")
End If
If Len(UserInput) 1 Then
Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput,
2)
End If
r = Target.Row
If Cells(r, 9) = "" Or Cells(r, 10) = "" Then
Cells(r, 11).ClearContents
Else
Cells(r, 4).FormulaR1C1 = "=MOD(RC[-1]-RC[-2],1)"
End If
End If
Application.EnableEvents = True
End Sub


--
RudeRam


------------------------------------------------------------------------
RudeRam's Profile: http://www.excelforum.com/member.php...fo&userid=8818
View this thread: http://www.excelforum.com/showthread...hreadid=555575