PDA

View Full Version : Solved: Quick VBA Userform Pivot query



Phelony
07-14-2011, 05:56 AM
Just a quick on, but could someone suggest what this (maddening) problem is?

I'm creating a userform to operate a pivot table, and appear to have written the filter command incorrectly.

With ActiveSheet.PivotTables("PivotTable1").PivotFields(ListBox1)


Listbox1 is the variable, but I can't for the life of me figure out why it's not working, I keep getting "unable to get the PivotFields propert of the PivotTable class" as runtime error (1004). :banghead:

I've tried naming the variable, but keep getting the same issue, I've tried naming the listbox, and that doesn't work either, I've even tried getting the listbox to populate a cell range and then have the cell range populate the variable, but guess what, that doesn't work either...

Any suggestions?

Aflatoon
07-14-2011, 05:58 AM
It would appear the field name does not match the value of your listbox.

Phelony
07-14-2011, 06:09 AM
I've just checked that by getting the listbox to populate a cell, it's showing the same as the fieldname as far as I can tell. :dunno

Aflatoon
07-14-2011, 06:14 AM
Are you sure there are no leading or trailing spaces in one or the other? How is the listbox populated? Also, is the field name a date?

Phelony
07-14-2011, 06:21 AM
There aren't any spaces before or after in either the fieldname or the listbox names, that was my first thought when you said about fieldnames not matching.

No dates, just dull phrases like "FRL Name" and "Manager Name".

The could the spaces in the phrases be having an effect?

I've recorded a macro for this to see what it did and the names came up unmodified and with spaces as I have them listed (the listbox was populated with the column headers the pivot table is drawn from).

The only difference was that where I have (listbox1) the recording had the full fieldname.

Aflatoon
07-14-2011, 06:25 AM
If you run this, does the error still occur? and does the name look correct?


Dim strField as String
strField = Listbox1.Value
With ActiveSheet.PivotTables("PivotTable1").PivotFields(strField)

Phelony
07-14-2011, 06:29 AM
:clap:

.value did it.

I had previously set them as named variables, but I didn't .value them.


grrrr....it's always the little things!

Thank you so much, I think my headache is clearing! :rotlaugh: