PDA

View Full Version : [SOLVED] Selecting Multiple Items in a Pivotfield, Hoping to be able to Toggle with a Hotkey



jmutsche
09-25-2013, 01:26 PM
Sub esis()
Dim pf As PivotField
Dim pi As PivotItem
'Application.ScreenUpdating = False

Set pf = ActiveSheet.PivotTables("PivotTable4").PivotFields("TRAN_GROUP")
With pf

For Each pi In pf.PivotItems

If pi.TRAN_GROUP = "ESIS" _
Or pi.TRAN_GROUP = "ESISFF" _
Or pi.TRAN_GROUP = "ESISFFC" _
Or pi.TRAN_GROUP = "ESISMN" _
Or pi.TRAN_GROUP = "PMIGEN1ESIS" Then
pi.Visible = True
Else
pi.Visible = False

End If
Next pi
End With
'Application.ScreenUpdating = True
End Sub

this is what I have and for some reason I cant get it to work. I get the error 438 object does not support this property or method.

can anyone help?

Aflatoon
09-26-2013, 05:37 AM
Pivotitems do not have a TRAN_GROUP property. Try:

Select Case pi.Caption
Case "ESIS", "ESISFF", "ESISFFC", "ESISMN", "PMIGEN1ESIS"
pi.Visible = True
case Else
pi.Visible = False
End Select

jmutsche
09-26-2013, 05:55 AM
Pivotitems do not have a TRAN_GROUP property. Try:

Select Case pi.Caption
Case "ESIS", "ESISFF", "ESISFFC", "ESISMN", "PMIGEN1ESIS"
pi.Visible = True
case Else
pi.Visible = False
End Select


Sub esis2()

Dim pf As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False

Set pf = ActiveSheet.PivotTables("PivotTable4").PivotFields("TRAN_GROUP")
With pf
Select Case pi.Caption
Case "ESIS", "ESISFF", "ESISFFC", "ESISMN", "PMIGEN1ESIS"
pi.Visible = True
Case Else
pi.Visible = False
End Select
End With

End Sub

still not working object variable or with block variable not set. I really appreciate the help!!

jmutsche
09-26-2013, 05:57 AM
I am in 2007 also if that makes a difference.

Aflatoon
09-26-2013, 06:31 AM
You shouldn't have removed the loop:
Sub esis()
Dim pf As PivotField
Dim pi As PivotItem
'Application.ScreenUpdating = False

Set pf = ActiveSheet.PivotTables("PivotTable4").PivotFields("TRAN_GROUP")
With pf

For Each pi In pf.PivotItems

Select Case pi.Caption
Case "ESIS", "ESISFF", "ESISFFC", "ESISMN", "PMIGEN1ESIS"
pi.Visible = True
Case Else
pi.Visible = False
End Select
Next pi
End With
'Application.ScreenUpdating = True
End Sub

jmutsche
09-26-2013, 06:56 AM
Thanks so much!!

you = VBA super hero

Aflatoon
09-26-2013, 07:16 AM
Not really, but thank you! :)