Strange loop...well, it appears to be.

[LaurieH]

Can anyone explain why this seems to loop back to the beginning of the "Sub" and refuses to go to the next line. The problem occurs at the highlighted line. Once executed, at the end of the offending line< it "loops" back to the "Private Sub Worksheet_Change" line. I accept my coding is not the best, but am teaching/learning by myself.
I am using Excel 2007.


Private Sub Worksheet_Change(ByVal Target As Range)


Dim Mon_TTH As Date, TDecimal
Dim Result As Single
Rem Dim Ftime As Integer
Rem Dim Stime As Integer
'Do nothing if more than one cell is changed or content deleted

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub



If Target.Address = "$G$7" Then

'Ensure target is a number before multiplying by 2

If IsNumeric(Target) Then

'Stop any possible runtime errors and halting code

On Error Resume Next

'Turn off ALL events so the Target * 2 does not put the code into a loop.

Application.EnableEvents = False

Target = Target * 2

'Turn events back on

Application.EnableEvents = True

'Allow run time errors again

On Error GoTo 0

End If

End If





Monday:
Mon_Stime = Worksheets("Timesheet_Entry").Range("E7").Value
Mon_Ftime = Worksheets("Timesheet_Entry").Range("G7").Value

Mon_TTH = TimeValue(Mon_Ftime) - TimeValue(Mon_Stime)

Rem If Mon_TTH > #2:00:00 AM# Then Mon_TTH = #2:00:00 AM#
Mon_TTH = Format(Mon_TTH, "hh:mm")
TDecimal = Split(Mon_TTH, ":")
Result = TDecimal(0) + ((TDecimal(1) * 100) / 60) / 100
Result = Format(Result, "##0.00")
MsgBox Result

Worksheets("Timesheet_Entry").Range("H7").Value = Result

If Worksheets("Timesheet_Entry").Range("H7").Value > "8" Then Worksheets("Timesheet_Entry").Range("I7").Value = "8.0"

End If

[Norie]

You are changing something on the sheet, therefore the sheet's change event will be called.

PS Can you add code tags when posting code?

[LaurieH]

Wow, a very quick reply...thnx. What you say makes sense, now I need to find a work around.

Once again...thanks

[Norie]

You've kind of got the solution in the first part of the code where you turn off events.

[LaurieH]

Thanx Norie, you are correct, have turned "off" events, then when values have been updated, have turned them back on.... all is good, I have so much to learn.