Creating dynamic Range for combobox

[lucky245]

I have a combo box on a worksheet named "Dashboard" which draws its fields from a range on a "Wardinfo" sheet. The list of wards will grow as wards enter data but until then I want the combobox list to only display wards which have already entered data.

I wanted to create a dynamic range which would then populate the combobox. Using the dynamic range code selects the range but the subsequent code ignores it. Only if I use the "Create range in Top row" manually does it work. Is there anyway to do this in vba allowing it to be a dynamic range?

Have uploaded where I am at incase my explanation doesn't make sense.

[Bob Phillips]

Specify what you mean by '... the subsequent code ignores it.'

[lucky245]

The Code in the Worksheet "support" for the range selWard

=OFFSET(INDIRECT(selRegion),resSelWard-1,0,1,1)

comes back with an invalid cell reference error if I use the dynamic range whereas it works perfectly with the "Create range in Top row" method.

Don't fully understand the coding here as I am newbie and was tasked to try to make the whole thing more dynamic so I am learning as I go ahead.

[Bob Phillips]

I still don't know what your problem is. The formula in Support only asks for one value, you get one value. Change the number, you get a different value.

And what is the "Create range in Top row" method?

[lucky245]

if you go to Insert->name->create you get Create names in: Top row,or Left column etc.

By hightlighting your list including the range name and then choosing Top row, the range is named exactly like the Top of the lists text.

I used this method for defining ranges for RVH , BCH

I have tried a different method for MIH and BHSCT which should allow dynamic ranges using =WardInfo!$K$3:INDEX(WardInfo!$K:$K,COUNTA(WardInfo!$K:$K)) where K is the column for BHSCT and K3 is the starting cell for the options.

If you now go to the Dashboard Worksheet and Select RVH or BCH and then a ward the code works, if however you choose MIH or BHSCT and then a ward there is an error.
I have uploaded the revised workbook with both options in.

Hope this is bit clearer

[Bob Phillips]

I don't get the error. I got a pivot error for Other, because there are no wrads, but none ofthe others.

[lucky245]

I have ask 4 other colleagues to test this on their PC's / Laptops and they all receive the same error as myself. We are using both Office XP in Xp and Office 2007 in Vista. If you choose RVH or BCH the selected hospital changes the graph, however if you choose BHSCT or MIH the list populates but on choosing a hospital you get an error stating Runtime error 13 (in Office 2007) or 1004 (Unable to set the _default property of the PivotItem class (in XP)
Thanks

[Akiba]

The columns & Counting thread I started might help with your create names issue