Combobox Losing Input Range

[gsbelbin]

Hi
I have a combobox on a chart which is linked to a cell on a worksheet. Changing the value in the combo updates the cell and then runs a procedure which updates the chart data. The Input Range for the combo is a dynamic named range and everything works exactly as planned. However when I save and close the workbook the combo loses the Input Range. I've tried it with a fixed named range and it does the same thing. The only way it will remember the Input Range is when it is set to a range of cell address eg CampaignData!$A$2:$A$6.

Any ideas?

Cheers

Gordon

 

[gsbelbin]

To get around this "feature" I can probably populate the Input Range each time the file is opened. What I don't know is how can I refer to the combobox in my VBA code. I've given it a name on the chart sheet but whichever way I try to update its RowSource property it comes back with "Object required".

Cheers

Gordon

 

[gsbelbin]

Sorted!

I put this in an auto_open() procedure

Charts("Results Chart").DropDowns(1).ListFillRange = "Campaigns"

where Campaigns is the dynamic named range

 

[mdfav]

Hi Gordon,

I am running into this issue as well. I found a way around this without VBA by creating the combo box in a worksheet first, then copying it to the chart. This causes Excel to prefix the name of the dynamic range with the workbook name, and it will now "remember" the input range when file is reopened.

- Mike

 

[gsbelbin]

Hi Mike
Thanks, I'll try that as well. I've had other problems with this that I've had to work around. I also have radio buttons and check boxes on the chart and they kept losing their connections to macros when I closed the file. Not every time, it just seems to happen at random!! So I've also had to assign the macros in the auto_open. Challenging but it works now.

Gordon