Pivot Table won't update with multiple selections

[megnin]

I'm having several probles with a pivot table. (I'm using Excel 2007 and saving in Excel 97-2003 compatibility mode.)

I have a Pivot Table with a Page Field named "ReviewDate".
The items which come from a Data sheet are "2008-01", "2008-02", "2008-03", etc... An item for each month, 2008 then 2009.

The first issue is if I check the "Select Multiple Items" check box in the Page Field Items drop-down and select any combination of one or more items checkbox, the pivot table does not update; it continues to show (All). If I clear the "Select Multiple Items" check box then I can select one item and it updates the Pivot Table fine. Select Multiple Items doesn't work.

Info: The Pivot Table is on a sheet called "PivotTable" and the data is on a sheet called "Data". Data gets the data from a SQL database through a connection to a Stored Procedure.

The second, related issue, is I'm trying to select multiple items in that ReviewDate page field using VBA and a ComboBox to select the range of dates to use. The ComboBox works and it selects the correct items in the ReviewDate page field, but the Pivot Table does not update. I'll select a Quarter with the combo box and the correct three months are selected when I look at the drop-down but the pivot table never changes.

VBA:

Private Sub ComboBox1_Change()
ShowDates
End Sub

Sub ShowDates()
Dim CB As OLEObject
Dim qtr As Range
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim lngIndex As Long
Dim dteStart As Date, dteEnd As Date

Set CB = ActiveSheet.OLEObjects("Combobox1")
Set qtr = ActiveSheet.Range("AD2:AD5").Find(CB.Object.Text, LookIn:=xlValues)
dteStart = qtr.Offset(0, 1).Value
dteEnd = qtr.Offset(0, 2).Value

Set pt = ActiveSheet.PivotTables(1)
' change field name as required
'Set pf = pt.RowFields("Date")
Set pf = pt.PivotFields("ReviewDate")
' speed up processing
Application.ScreenUpdating = False

' pt.ManualUpdate = True

' Need at least one visible item
pf.PivotItems(1).Visible = True

For lngIndex = 2 To pf.PivotItems.Count
Set pi = pf.PivotItems(lngIndex)
pi.Visible = (pi.Value >= dteStart And pi.Value <= dteEnd)
Next lngIndex
Set pi = pf.PivotItems(1)
On Error Resume Next
pi.Visible = (pi.Value >= dteStart And pi.Value <= dteEnd)
If Err.Number <> 0 Then
MsgBox "No items match date range!"
End If
End Sub


The other issue is I get a Type Mismatch error on this line even though I've seen this code in other peoples solutions with no mention of errors:
pi.Visible = (pi.Value >= dteStart And pi.Value <= dteEnd)

I'd greatly appreciate some help figuring out what I'm doing wrong here.
Thank you!
David

 

[megnin]

I added

pt.RefreshTable

and the table flashes an extra time but still does not update. Is this a "Feature"?

 

[megnin]

Can't anyone tell me how to select multiple items in a pivot table and it work?
This is killing me. I've spent a week just trying to get a pivot table to show me data for a three month period by selecting three months in the items list. I need to do it with VBA, but it doesn't even work doing it manually.

 

[megnin]

There must have been some corruption involved. We did have a blackout earlier in the week, common here in Southern Florida, that shut down my computer and required the file to be recovered.
I started a new workbook and copied the code over and it seems to be working fine now.
When all else fails, start over.