Control in Chart Sheet

[Anderflash]

Well, I put a chart sheet with a chart that depends on the scroll bar
control. This control is also on the Chart sheet. I change the value of the
scroll bar, it changes the value of the link cell and finally the link cell
modifies the chart.

1) If a put a name to the cell (like 'variable') and put 'variable' on the
link cell field, when I save, close and open the .xls, the link cell field
will be empty.
2) If a put Sheet1!A1 on the link cell field (supposing that my cell is A1)
and save, close and open the .xls, the link cell field will be correct (it
will have Sheet1!A1).

The item '1)' occurs if the scroll bar is on the chart sheet. If I put the
scroll bar on the worksheet, it won't fail.

Why do the controls of the Form Bar that have link cell field and are on a
Chart Sheet fail when I put a named range?

I hope my question is clear to understand.

PS: Do a chart sheet (can be empty - F11 on a empty cell), put a check box
from the Form Bar, put a name on cell A1 (variable), put variable on the link
cell of the check box. Save, close and open. See whether variable is at the
link cell field.
Now put Sheet1!A1 on the link cell field. Save, close......and so on.

 

[Andy Pope]

Hi,

Interesting. Using xl2003 I was able to reproduce the problem.

2 forms checkboxes on a chartsheet.
First linked to range Sheet1!$A$1
Second to named range MyCk which in turn is referenced to Sheet1!$A$1

On saving and re opening workbook the second checkbox is no longer
referencing MyCk.

I did find though that creating a sheet level named range,
Sheet1!MyCk which references Sheet1!MyCk
did retain the link.

Cheers
Andy

 

[Jon Peltier]

As with any links from a non sheet object to a name, you need the sheet or
workbook name. I named a cell MyCell, then set up the link to the scollbar
to Sheet1!MyCell. As it was a workbook-level name, the link was converted to
Book1.xls!MyCell.

- Jon

 

[Andy Pope]

Hi Jon,

How or what converted the named range to workbook level name?
When I entered just the named range it worked but was not retained upon
saving. It did not alter to Book1!MyCell automatically.

Cheers
Andy

 

[Jon Peltier]

Sheet1!MyCell was converted to Book1.xls!MyCell. If you skip the sheet or
workbook name, when the workbook is saved and reopened, it has forgotten the
link. Excel's smart enough to not let you assign a cell address without a
sheet name prefix, but it allows you to enter a defined name without such a
prefix, then forgets later where the name is located.

- Jon

 

[Andy Pope]

Thanks for the clarification Jon.

Must admit I had not encountered this potential trap before.

Cheers
Andy

 

[Jon Peltier]

I'm so used to always entering a qualifier for a name, because charts always
require one, that I never even think of trying to enter a name without one.

- Jon