List box on chart page (goes blank when re-ranged)

[ETID]

Hi,

Has anybody seen this...

I have a chart that is it's own sheet, on that sheet (on top of the chart) there is a list box (from forms tool)

It works fine until I want to change the input range for the box, then the box no longer displays the items in the range.

If I create a new list box,..then all is well again.

:-(

 

[SkipVought]

ETID,

I don't understand.

I created a Forms Listbox on a Chart Sheet and referenced the Input Range.

Made a selection

Edited the Listbox and changed the Input Range

Made a selection

???

Skip,

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

 

[ETID]

Hmmm...the difference might be that this particular chart is a pivot chart.

 

[SkipVought]

Works with a PivotChart too.

What kind of range are you referencing? How are you instantiating the list range?

Skip,

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

 

[ETID]

Nothing fancy...just right click >format >select input range,...there is a macro associated with it but the macro does not ref. the list box properties, only captures the selection via the target cell declared in the format of the list box, then passes a vlookup value to the pivot chart as a pivot field selection.

(there are reasons why I don't just use the drop downs in the pivot)

The data for the pivot is based on a sheet that is linked via msquery, resluting in a moving target of record counts.

So I have to re range the list box accordingly either manually or via vba.

Are we talking about the same list box? (from the forms toolbar)

 

[SkipVought]

Yes, Forms Listbox.

Why not try using the OFFSET function
[tt]
=OFFSET(anchor_ref, row_offset, col_offset, nbr_rows, nbr_cols)
[/tt]
where the row_offset and/or col_offset is varied. Then you don't have to reset the list range at all.

Skip,

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

 

[ETID]

Do I declare that as the list box range?

 

[SkipVought]

use Insert/Name/Define

type a NAME in the Names in workbook textbox and the offset formula in the refersto textbox.

the use the NAME in the input range

Skip,

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

 

[ETID]

Thanks for that...All worked well until I decided to resize the List, then BOOM blank ListBox again!

@#*&(*&^$$&*&!!!...Bill Gates :-(