Jari Haaranen


Hi there,

I have problem with pivot table Visible property in excel 2003 SP2 vba."run-time error 1004: Unable to Set Visible Property"
VBA code have a Pivot Item property for filtering pivot data.
Pivot item is "DeliveryDay". In code i check that Visible value of items (true/false) and if it is False then i like to change it to True.
Code using For Each loop for checking Visible proberty value. (If PivItem.Visible = False Then PivItem.Visible = True)
I'am really sure that i have right syntax in my code. I'am using similar code in other PivotSheet and it's works :-|

Following article tells at that is an Excel bug. I don't have any sorting in pivotTable. Article is over one year old, so i loaded SP2 for this case -> No help.

Is there any hotfix/solution for this case

"Is an Excel bug that set PivotItem.Visible to true will generate 1004 Macro Error
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=168090&SiteID=1"


cheers, Jari




Re: problem with pivot table Visible property in excel 2003 SP2 vba

ADG


Hi

can you post your code, just in case there is an obvious error / typo

Regards

ADG






Re: problem with pivot table Visible property in excel 2003 SP2 vba

Jari Haaranen

Hi ADG,

This problem has been solved. I still don't understand why following (Problem code) won't work.

thanks anyway, jha

Sub filterOpenBeDelayedData(pivotname As String, currentmonth As String, currentyear As Integer)

Dim PivItem As PivotItem
Sheets("OpenBeDelayed").Select


' *** Problem code
' For Each PivItem In _
'ActiveSheet.PivotTables(pivotname).PivotFields("DeliveryDay").PivotItems
' If PivItem.Visible = False Then
' PivItem.Visible = True
' End If
'Next PivItem

' *** Problem code end


For Each PivItem In _
ActiveSheet.PivotTables(pivotname).PivotFields("DeliveryDay").PivotItems

If CDate(PivItem.Name) < Date Then
If PivItem.Visible = False Then
PivItem.Visible = True
End If
End If
If CDate(PivItem.Name) >= Date Then
PivItem.Visible = False
End If

Next PivItem

End Sub