paddysheeran
05-02-2013, 05:05 AM
Hi all,
Im having real trouble with a Worksheet Calculate macro:
When I change any cell in the sheet to fire the calculation the code works fine.
When I try to use an autofilter on the data set in the same sheet the code hangs and the screen freezes. The only way to regain access to the excel workbook is to press F8 in the code window. I've no idea why this is happening:
Public SLA_Type, SLA_Type_2 As String
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim SL_Types As Variant
For Each SL_Types In Array( _
"SLA_16_G_F_S1", "SLA_16_G_F_S2", "SLA_16_G_F_S3", "SLA_16_G_F_S4" _
, "SLA_16_A_F_S1", "SLA_16_A_F_S2", "SLA_16_A_F_S3", "SLA_16_A_F_S4" _
, "SLA_16_E_F_S1", "SLA_16_E_F_S2", "SLA_16_E_F_S3", "SLA_16_E_F_S4" _
, "SLA_16_L_F_S1", "SLA_16_L_F_S2", "SLA_16_L_F_S3", "SLA_16_L_F_S4")
SLA_Type = SL_Types
Process_Falures
Next SL_Types
For Each SL_Types_2 In Array( _
"SLA_17_G_F_S1", "SLA_17_G_F_S2", "SLA_17_G_F_S3", "SLA_17_G_F_S4" _
, "SLA_17_A_F_S1", "SLA_17_A_F_S2", "SLA_17_A_F_S3", "SLA_17_A_F_S4" _
, "SLA_17_E_F_S1", "SLA_17_E_F_S2", "SLA_17_E_F_S3", "SLA_17_E_F_S4" _
, "SLA_17_L_F_S1", "SLA_17_L_F_S2", "SLA_17_L_F_S3", "SLA_17_L_F_S4")
SLA_Type_2 = SL_Types_2
Process_Falures_2
Next SL_Types_2
ActiveSheet.Range("A1").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Sub Process_Falures_2()
If Application.WorksheetFunction.Sum(Sheets("SLA#17").Range(SLA_Type_2)) > 0 Then
ActiveSheet.Shapes.Range(Array(SLA_Type_2)).Select
'Change Oval to Red
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset24
Else
'Change Oval to Green
ActiveSheet.Shapes.Range(Array(SLA_Type_2)).Select
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset25
End If
End Sub
Sub Process_Falures()
'Global
'Set Activesheet_return = ActiveSheet
If Application.WorksheetFunction.Sum(Sheets("SLA#16").Range(SLA_Type)) > 0 Then
ActiveSheet.Shapes.Range(Array(SLA_Type)).Select
'Change Oval to Red
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset24
Else
'Change Oval to Green
ActiveSheet.Shapes.Range(Array(SLA_Type)).Select
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset25
End If
End Sub
Pulling my hair out with this one! any help would be greatly appreciated.
thanks,
Paddy.
Im having real trouble with a Worksheet Calculate macro:
When I change any cell in the sheet to fire the calculation the code works fine.
When I try to use an autofilter on the data set in the same sheet the code hangs and the screen freezes. The only way to regain access to the excel workbook is to press F8 in the code window. I've no idea why this is happening:
Public SLA_Type, SLA_Type_2 As String
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim SL_Types As Variant
For Each SL_Types In Array( _
"SLA_16_G_F_S1", "SLA_16_G_F_S2", "SLA_16_G_F_S3", "SLA_16_G_F_S4" _
, "SLA_16_A_F_S1", "SLA_16_A_F_S2", "SLA_16_A_F_S3", "SLA_16_A_F_S4" _
, "SLA_16_E_F_S1", "SLA_16_E_F_S2", "SLA_16_E_F_S3", "SLA_16_E_F_S4" _
, "SLA_16_L_F_S1", "SLA_16_L_F_S2", "SLA_16_L_F_S3", "SLA_16_L_F_S4")
SLA_Type = SL_Types
Process_Falures
Next SL_Types
For Each SL_Types_2 In Array( _
"SLA_17_G_F_S1", "SLA_17_G_F_S2", "SLA_17_G_F_S3", "SLA_17_G_F_S4" _
, "SLA_17_A_F_S1", "SLA_17_A_F_S2", "SLA_17_A_F_S3", "SLA_17_A_F_S4" _
, "SLA_17_E_F_S1", "SLA_17_E_F_S2", "SLA_17_E_F_S3", "SLA_17_E_F_S4" _
, "SLA_17_L_F_S1", "SLA_17_L_F_S2", "SLA_17_L_F_S3", "SLA_17_L_F_S4")
SLA_Type_2 = SL_Types_2
Process_Falures_2
Next SL_Types_2
ActiveSheet.Range("A1").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Sub Process_Falures_2()
If Application.WorksheetFunction.Sum(Sheets("SLA#17").Range(SLA_Type_2)) > 0 Then
ActiveSheet.Shapes.Range(Array(SLA_Type_2)).Select
'Change Oval to Red
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset24
Else
'Change Oval to Green
ActiveSheet.Shapes.Range(Array(SLA_Type_2)).Select
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset25
End If
End Sub
Sub Process_Falures()
'Global
'Set Activesheet_return = ActiveSheet
If Application.WorksheetFunction.Sum(Sheets("SLA#16").Range(SLA_Type)) > 0 Then
ActiveSheet.Shapes.Range(Array(SLA_Type)).Select
'Change Oval to Red
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset24
Else
'Change Oval to Green
ActiveSheet.Shapes.Range(Array(SLA_Type)).Select
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset25
End If
End Sub
Pulling my hair out with this one! any help would be greatly appreciated.
thanks,
Paddy.