How do I set CurrentPage value in pivot table from drop down box?

[searlerm]

I have a pivottable with "Date" as the page field and this feeds into a drop-down combo box on another sheet via a macro. I want to be able to change the currentpage value in the pivot table by selecting a different date from the combo box but can't seem to think of the macro required. Can anybody help please?

Thanks

 

[xlbo]

Use the combobox CHANGE event to pick up the date in the combobox

myDate = ComboBoxName.value


Then insert that into the page field of the combobox:

Pivottables("PTName").Pagefields(PageFieldName).value = myDate

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[searlerm]

Hi Geoff

Thanks for the reply. The combo box I have used is created by the forms toolbar in Excel rather than through VB. I have tried to put a Private Sub as a Declaration in the worksheet in which the combo box appears as follows:

selectedDate = Worksheets("Control").Date_Selection_Box.Value

Worksheets("Control").PivotTables("Pensions").PageFields(Date).Value = selectedDate

It comes back with a "run time error 438 - Object doesn't support this property or method" message.

What am I doing wrong?

Richard

 

[SkipVought]

selectedDate =Application.Index([MyList], ActiveSheet.Shapes(1).DrawingObject.Value, 1)

where MyList is a named range referring to my list.

The reason being that the dropdown returns the INDEX of the selection rather than the SELECTION.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884