megnin
Active Member
- Joined
- Feb 27, 2002
- Messages
- 340
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
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