We help IT Professionals succeed at work.

VBA to filter Excel PivotTable PageField by Date Range with Combo Box (Form Control)

megnin
megnin asked
on
4,322 Views
Last Modified: 2013-11-10
I have a PivotTable and PivotChart I'm trying to add a ComboBox to allow a user to select a date range by Quarter to display.

The date data is in the format "2009-11" for November '09.  I need to select three month ranges, like 2009-07 through 2009-09.  With the correct range selected in the PageField of the pivot table the chart will show the select Quarter data.

I want to have a ComboBox on the chart to select the Fiscal Quarter to view.

So far I've tried this macro, but I get a "Type mismatch" error when I run it with "01/01/2007" in G2 and "07/01/2009" in H2:

Sub ShowDates()
   Dim pt As PivotTable
   Dim pf As PivotField
   Dim pi As PivotItem
   Dim lngIndex As Long
   Dim dteStart As Date, dteEnd As Date
   
   ' set date range
   With Sheets("PivotTable")
      dteStart = .Range("G2").Value '+ 0
      dteEnd = .Range("H2").Value '+ 0
   End With
   
   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
   
   ' enable updates
   pt.ManualUpdate = False
   Application.ScreenUpdating = True
End Sub

Sometimes I don't get the type mismatch error and it just seems to hide everything; my PivotTable is empty and shows no data at all, even though I have records in that date range.

I tried the code below and I'm sure the "Case 1" and "Case 2" are firing when I select 1 or 2 using a ComboBox but the chart never changes.  If I select the items in the pivot table manually it works, of course, but neither the pivot table or the chart update when the macro fires as a result of the ComboBox change.  I'm sure the ComboBox is working because I had it set a flag to tell me "1" or "2" depending on the option selected.
Sub DropDown6_Change()
'
' DropDown6_Change Macro
   Application.ScreenUpdating = False
Dim pvtItem As PivotItem
'Dim strSupplier As String
'Dim blnMatch As Boolean
    
Select Case Worksheets("PivotTable").Range("D2")
Case 1
    'Sheets("PivotTable").PivotTables("PivotTable1").PivotFields("ReviewDate").CurrentPage = "(All)"
    With Sheets("PivotTable").PivotTables("PivotTable1").PivotFields("ReviewDate")
        For Each pvtItem In .PivotItems
            pvtItem.Visible = True
            'If StrComp(strSupplier, pvtItem.Value, vbTextCompare) = 0 Then blnMatch = True
        Next
    End With
Case 2
    With Sheets("PivotTable").PivotTables("Pivottable1").PivotFields("ReviewDate")
        .PivotItems("2007-02").Visible = False
        .PivotItems("2007-06").Visible = False
        .PivotItems("2007-07").Visible = False
        .PivotItems("2007-08").Visible = False
        .PivotItems("2007-09").Visible = True
        .PivotItems("2007-10").Visible = False
        .PivotItems("2007-11").Visible = False
        .PivotItems("2007-12").Visible = False
'        .PivotItems("2008-01").Visible = False
        .PivotItems("2008-02").Visible = False
        .PivotItems("2008-03").Visible = False
'        .PivotItems("2008-04").Visible = False
'        .PivotItems("2008-05").Visible = False
        .PivotItems("2008-06").Visible = False
'        .PivotItems("2008-07").Visible = False
        .PivotItems("2008-08").Visible = False
'        .PivotItems("2008-09").Visible = False
'        .PivotItems("2008-10").Visible = False
'        .PivotItems("2008-11").Visible = False
'        .PivotItems("2008-12").Visible = False
        .PivotItems("2009-01").Visible = False
        .PivotItems("2009-02").Visible = False
        .PivotItems("2009-03").Visible = False
        .PivotItems("2009-04").Visible = False
        .PivotItems("2009-05").Visible = False
        .PivotItems("2009-06").Visible = False
        .PivotItems("2009-07").Visible = False
        .PivotItems("2009-08").Visible = False
        .PivotItems("2009-09").Visible = False
        .PivotItems("2009-10").Visible = False
'        .PivotItems("2009-11").Visible = False
'        .PivotItems("2009-12").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
Case 3
Case 4
Case 5
Case 6
Case 7
Case 8
Case 9
Case 10
Case 11
 
End Select
 
   Application.ScreenUpdating = True
 
End Sub

Open in new window

Comment
Watch Question

Author

Commented:
I added ActiveWorkbook.RefreshAll to the DropDown6_Change() but aside from taking two minutes to recalculate, it does nothing.

Author

Commented:
Nobody?
Okay, forget everthing above.  All I want to know is how to use a ComboBox to change a pivot table.

Commented:
Here you go.  This will update the pivottable using an ActiveX control ComboBox, but I modified your approach to use a lookup into a table to find the start and end ReviewDate values.  Also, I'm using strings like "2009-01" for January, if your source data for the pivot is different, then you need to modify my cells P2:Q5 so the lookup values match the datatypes you are using.

I'm basing this all on your macro.  I am using the combobox1_change() event macro.

Let me know if this will work.

Tom.
EE-24887718.xls

Author

Commented:
Thanks Tom!  I'm working with Excel 2007 because that's what I have, but I saving in Excel 97-2003 because that's what everyone else in my office has.  I don't know if that's why, but my ActiveX controls are greyed out, so I have a Forms Control ComboBox.
I think as long as I can do it with a Forms Controls ComboBox it should work.  
Do you know why my existing macro would change the page filter of the pivot table but the table would not update to reflect the selections?
I added this (to my original macro) and it made no difference:
Dim pt As PivotTable
Set pt = Sheets("PivotTable").PivotTables("PivotTable1")
pt.RefreshTable
 

Author

Commented:
I'm not sure why, but on another sheet with a copy of my Chart I was able to insert an ActiveX ComboBox, so that's good.

Author

Commented:
I had my Chart, and so also the ComboBox, on a different sheet but for simplicity I moved them onto the same sheet as the Pivot Table.  My O2:O5 range was already used so I put the lookup table in AD2:AD5 and adjusted the references.
When I select a Quarter with the ComboBox I get a Type mismatch error on this line:
pi.Visible = (pi.Value >= dteStart And pi.Value <= dteEnd)
The items, 2009-1, 2009-2 and 2009-3 are selected in the pivot table regardless of the error, but the pivot table does not update or refresh to show the new selections.  Also the first item and last item (Blank) both remain selected.  This is encouraging progress.

Author

Commented:
Well, I've seen the exact same code in other posts and the other people using it don't seem to be having the same problem.  What could I have set differently causing a Type mismatch error.  I'm using Excel 2007 but saving in Excel 97-2003 compatibility mode.

Commented:
You are having issues with ActiveX controls because you haven't gone into Options, Trust Center,  Trust Center Settings, ActiveX Settings, and enabled them.

I need to know the datatype of the ReviewDate field, and is this a column in your source table, or have you grouped a date field in the pivottable?
For Strings, the items should be 2009-01, not 2009-1, as the sort order is important for the evaluation of

pi.Visible = (pi.Value >= dteStart And pi.Value <= dteEnd)


What may be happening is that the items themselves are being renamed, because the values you are passing to the pivottable don't exist in the pivotitems for the field.  As an example, if the items are
2009-01, 2009-02, 2009-03, etc

and through VBA you attempt to set the currentpage to 2009-1, you risk renaming the selected currentpage item to 2009-1.  The pivottable does refresh, but the same items by index ( pivotitems(1) etc) are selected, with only the name of pivotitems(1) being changed, not the index, then the same results come back.

I don't know the root of this behavior, and how sometimes it happens and sometimes the pivottable returns an error.  I have used so many combinations of On Error statements that I don't have any clean QA test results.

Author

Commented:
The ReviewDate field is a custom "yyyy-mm" field so that I can select the dates by month and quarter in the pivot table.  I'm not married to that format if another will work.
I'm never seeing the dates like 2009-1 without the leading zero on the month.  It always shows 2009-01.  I'm not sure where you got that.
Even manipulating the pivot table manually if I have "Select Multiple Items" checked, then the pivot table does not reflect any selection changes I make.  It alway shows (All) or I uncheck multiple items and select only one month and it will update to show data for that one month.  I'm reporting my quarter so I need to select three months.

Author

Commented:
In the Trust Center I have this option selected for ActiveX Settings for all Office Applications:
Prompt me before enabling all controls with minimal restrictions.
And Safe mode check box is checked.
When I open the workbook I have to click the button to enable macros.

Commented:
I apologize for any confusion, but you haven't answered my question about ReviewDate, is it coming from the data source as a column? And, in the datasource, what datatype is it? Is it a date that is formatted as YYYY-DD?  If so, then the mismatch is because I suggested you use strings in the lookup range AD2:AD5, you should change that to 1/1/2009 3/31/2009 etc

so that

pi.Visible = (pi.Value >= dteStart And pi.Value <= dteEnd)

works properly.

Author

Commented:
Sorry.  Yes ReviewDate comes from the datasource as a column.  In the SQL table it's a smalldatetime.  On the Data worksheet I format it to a yyyy-dd date.
I see.  I'll give that a try.  
As for the pivot table.  I can't even select three months in the filter field manually.  If "Select Multiple Items" is checked the pivot table doesn't update.  I have to uncheck "multiple items" and only pick one month for it to even work manually.

Author

Commented:
Oh, I was mistaken about how I format the dates to 2009-11 format.  My stored procedure formats it on the way to the worksheet with:
"LEFT(convert(char(10), ReviewDate, 127), 7) as ReviewDate"

Author

Commented:
...so the (char(10) makes it a string.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
[Quote]
Go to your Data worksheet, change the title in the header row of any column you have been manipulating in VBA. Refresh the pivottable, which will cause the effected columns to be removed from the visible table.  The Field List will have the new column names.  Go back and restore the original column titles, I usually do this by refreshing the SQL query, which will restore the header row for me.  Refresh again, which will update the Field List with the correct field names, then put the fields back in the pivot where they belong.  This undo's everything the vba may have tangled.
[/Quote]
I did all that to the letter and when it was back to the way it was I tried selecting an item with the "multi" check box checked and if that box is check it doesn't even work if I only select one item.  That box must be cleared for the pivot table to update at all.  That's so weired.

Author

Commented:
I just copied the data from the data page (no sql connections) and made a simple pivot table from it and that worked fine with multiple selections.  I'm going to add the sql connection and see if that's what breaks it.

Author

Commented:
Well, I thought I copied only the data.  It copied the datasource as well, so in the new workbook I have the data page and sql connection and the new pivot table with no VBA or other junk. So, at least it doesn't seem to be the sql connection that breaks it.

Author

Commented:
I haven't saved the workbook yet.  I've been working in Excel 97-2003 compatibility mode so others in the office who use Excel 2003 can use it.  I'll see if that's what breaks it.

Commented:
I feel your pain!  I'm having an issue right now with different collections, one property seems to be read/write in collection A, but the same property, which reads the same value, apparently is read only in collection B.  This doesn't match Microsoft's own documentation, and I hate to write a work-around where one isn't needed, but I can't figure out how to write to the property using collection B.

Author

Commented:
I wish I could help you with that, but I'm just learning to program so I'm still struggling with each new thing I try to do.  The problem is my organization keeps wanting these crazy complex reports and stiff.  ;-(

Author

Commented:
I'm rebuilding everything in a new workbook and so far it's working okay.  I haven't begun to put the VBA back in but the pivot table seems to be okay.

Author

Commented:
Thank you very much.  We are in Southern Florida, the lightning capital of the world.  We had a power outage and my UPS failed.  Excel recovered my file but it apparently suffered some corruption.  Rebuilding it from scratch resulted in a file that worked.  Your comment did teach me some new tips.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.