PDA

View Full Version : Autofilter - Worksheet Calculate



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.

Bob Phillips
05-02-2013, 05:19 AM
Can you post the workbook?

paddysheeran
05-02-2013, 05:33 AM
Hi, sorry I cant as there is senstive customer data within it. Is anything in the code I've posted set out incorrectly?

snb
05-02-2013, 05:41 AM
I'd suggest to pass the value to the 'called' macro:



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


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


Private Sub Process_Falures(c00)
ActiveSheet.Shapes.Range(c00).ShapeRange.ShapeStyle =iif(Application.Sum(Sheets("SLA#16").Range(c00)) > 0, msoShapeStylePreset24, msoShapeStylePreset25)
End Sub

paddysheeran
05-02-2013, 06:07 AM
I'm getting a Run-Time error '438' Object doesn't support this property or method on the line:

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

paddysheeran
05-02-2013, 06:29 AM
it works using:

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

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
05-02-2013, 06:33 AM
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
05-02-2013, 08:28 AM
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
05-02-2013, 09:11 AM
Probably this is simpler


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