Autofilter - Worksheet Calculate

[paddysheeran]

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:

[vba]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

[/vba]

Pulling my hair out with this one! any help would be greatly appreciated.

thanks,

Paddy.

[Bob Phillips]

Can you post the workbook?

[paddysheeran]

Hi, sorry I cant as there is senstive customer data within it. Is anything in the code I've posted set out incorrectly?

[snb]

I'd suggest to pass the value to the 'called' macro:


[VBA]
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False

For Each it 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")
Process_Falures it
Next

For Each it 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")
Process_Falures_2 it
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
[/VBA]
[VBA]
Private Sub Process_Falures_2(c00)
ActiveSheet.Shapes.Range(c00).ShapeRange.ShapeStyle =iif(Application.Sum(Sheets("SLA#17").Range(c00)) > 0, msoShapeStylePreset24, msoShapeStylePreset25)
End Sub
[/VBA]
[VBA]
Private Sub Process_Falures(c00)
ActiveSheet.Shapes.Range(c00).ShapeRange.ShapeStyle =iif(Application.Sum(Sheets("SLA#16").Range(c00)) > 0, msoShapeStylePreset24, msoShapeStylePreset25)
End Sub
[/VBA]

[paddysheeran]

I'm getting a Run-Time error '438' Object doesn't support this property or method on the line:

[vba]ActiveSheet.Shapes.Range(c00).ShapeRange.ShapeStyle = IIf(Application.Sum(Sheets("SLA#16").Range(c00)) > 0, msoShapeStylePreset24, msoShapeStylePreset25)[/vba]

[paddysheeran]

it works using:

[VBA]ActiveSheet.Shapes.Range(Array(c00)).Select
Selection.ShapeRange.ShapeStyle = IIf(Application.Sum(Sheets("SLA#16").Range(c00)) > 0, msoShapeStylePreset24, msoShapeStylePreset25)[/VBA]

When the sheet calculates all the shapes on my sheet are non visible. Is there anyway to stop this from happening so the shapes remain visible throughout the calculation process?

[paddysheeran]

Also to expand my knowledge on this can you advise why you are using c00 in the Process_Failures subs and the relationship between "c00" and "it"? many thanks.

[Bob Phillips]

c00 is the argument of the second procedure, it is just a name given to that argument. As it (that is the variable it, not c00 or the procedure) is passed to the call to that procedure, c00 is it, they both refer to the same lump of memory.

[snb]

Probably this is simpler

[vba]
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False

For Each it 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")
ActiveSheet.Shapes(it).ShapeStyle = 25-ABS(Application.Sum(Sheets("SLA#16").Range(it)) > 0)
Next

For Each it 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")
ActiveSheet.Shapes(it).ShapeStyle = 25-ABS(Application.Sum(Sheets("SLA#17").Range(it)) > 0)
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
[/vba]