wilg
03-05-2011, 08:00 AM
Hi there, need help with a looping calculation. The code below loops where I have put a comment "looping" beside it.
I have other code that sort the worksheet but after I sort the worksheets I still need the worksheets which range is between 1-7 to still move to before my "1 MAINT" sheet. Any suggestions on preventing this loop?
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Select Case Sh.Name
Case "1 maint", "2 status", "3 summary" '<--Change to the sheetnames you do NOT want the code to run against.//
Case Else
With Sh
If .Range("as5") > 7 Then
.Tab.ColorIndex = 5 ' BLUE
Else
If .Range("as1") = "n" Then
.Tab.ColorIndex = 3 ' RED
.Move After:=Sheets(Sheets.Count)
ThisWorkbook.Sheets("3 summary").Select
Else
If .Range("as5") > 1 And .Range("AS5") < 7 And .Range("as2") <> "Informed" Then
MsgBox .Range("AS1").Value & vbNewLine & vbNewLine & " IS COMING UP TO THEIR ANNIVERSARY DATE." & vbNewLine & vbNewLine & "PLEASE PRINT OUT ATTENDANCE AND CLEAR CELLS TO START A NEW YEAR.", vbInformation,
.Tab.ColorIndex = 6 ' yellow
.Move before:=Sheets("1 maint")
.Range("as2").FormulaR1C1 = "Informed"
else 'LOOPING STATEMENT
if .Range(as5") >1 And .Range("as5") <7 then
.Move before;Shetts("1 maint")
end if
End If
End If
End If
End With
End Select
End Sub
Sorry for the untidy code. Still learning that one.
I have other code that sort the worksheet but after I sort the worksheets I still need the worksheets which range is between 1-7 to still move to before my "1 MAINT" sheet. Any suggestions on preventing this loop?
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Select Case Sh.Name
Case "1 maint", "2 status", "3 summary" '<--Change to the sheetnames you do NOT want the code to run against.//
Case Else
With Sh
If .Range("as5") > 7 Then
.Tab.ColorIndex = 5 ' BLUE
Else
If .Range("as1") = "n" Then
.Tab.ColorIndex = 3 ' RED
.Move After:=Sheets(Sheets.Count)
ThisWorkbook.Sheets("3 summary").Select
Else
If .Range("as5") > 1 And .Range("AS5") < 7 And .Range("as2") <> "Informed" Then
MsgBox .Range("AS1").Value & vbNewLine & vbNewLine & " IS COMING UP TO THEIR ANNIVERSARY DATE." & vbNewLine & vbNewLine & "PLEASE PRINT OUT ATTENDANCE AND CLEAR CELLS TO START A NEW YEAR.", vbInformation,
.Tab.ColorIndex = 6 ' yellow
.Move before:=Sheets("1 maint")
.Range("as2").FormulaR1C1 = "Informed"
else 'LOOPING STATEMENT
if .Range(as5") >1 And .Range("as5") <7 then
.Move before;Shetts("1 maint")
end if
End If
End If
End If
End With
End Select
End Sub
Sorry for the untidy code. Still learning that one.