Combo Box Change Event with Macro Call

First post ever.

I have a macro (Excel 2003) which refreshes a number of pivot tables based on values in three different cells on a separate worksheet. The macro runs fine when fired independently.

I added a dropdown list to select values for each of these cells and triggered the firing of my macro using a worksheet change event. This works perfectly.

What I really want is to use three combo boxes to select the values and to trigger the macro using a combobox change event. When I try this, however the macro bombs on the third line of code. I'm pretty new to VBA and I've never tried to use the combobox change event so I may be missing something really basic.

Here's the code I'm using for the change event. (The macro name is "RefreshPivots"). I've placed the following code on the worksheet object (within the VBA editor) for the worksheet where the combo box was placed. Again the macro runs perfectly when I run it on its own which leads me to suspect that there's something wrong with my combox change event code.

Sub Combobox1_Change()
             RefreshPivots
            End Sub

Here's a snippet of the macro code which refreshes the first of several pivot tables:

'Refresh pivots
'  This block refreshes the pivot cache of all pivot tables to deal
'   with problems where drop-down list for Page field "SPECIALTY" was getting
'   corrupted dropping items from list and adding an item "0" in the pivot table
'   Page field selection list. Code rebuilds the
'   pivots by
'       1) Removing items from the "Page" dimension of each pivot table
'       2) Refreshing the pivot cache
'       3) Adding the Page fields back
'       4) Supplying the user selected values for each of the three page fields
'
'    **********************************************************************
'    **********************************************************
'    ****                                                              ****
'    ****   1   Refresh Pivot Table:                          ****
'    ****       Filled_2_yrs_ago_Counts_by_Month   ****
'    ****       (Worksheet: Filled Reqs - Counts       ****
'    ****                                                              ****
'    **********************************************************
'    **********************************************************

'               ***************************************************
'   *************  (a) Refresh Cache
'               ***************************************************
    Sheets("Filled Reqs - Counts").Visible = True
    Sheets("Filled Reqs - Counts").Select
    
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("MED_CTR"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("RECRUITER"). _
        Orientation = xlHidden

    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotCache.Refresh ''

    With ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY")
        .Orientation = xlPageField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("MED_CTR")
        .Orientation = xlPageField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("RECRUITER")
        .Orientation = xlPageField
        .Position = 1
    End With

'               ***************************************************
'   *************  (b) Refresh Pivot with user-selected values
'               ***************************************************
   ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY"). _
        CurrentPage = Range("A1").Value

   ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("MED_CTR"). _
        CurrentPage = Range("A2").Value

   ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("RECRUITER"). _
        CurrentPage = Range("A3").Value

The line of code (executable line3) where the macro fails is:

    
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY"). _
        Orientation = xlHidden

The error is:

Run-time error '1004'
Unable to get the PivotFields property of the PivotTable class

Thanks for any help you can offer

Hi Bosco6 and welcome to the forum. Please take a moment to read the forum rules located here and wrap your code in code tags as per Rule #3. Once you do that, someone will be able to help you.