Control in Chart Sheet
 

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.

Control in Chart Sheet
 

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


Anderflash wrote:
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.

Control in Chart Sheet
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy Pope" wrote in message
...
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


Anderflash wrote:
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.

Control in Chart Sheet
 

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 wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy Pope" wrote in message
...

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


Anderflash wrote:

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.

Control in Chart Sheet
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy Pope" wrote in message
...
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 wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy Pope" wrote in message
...

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


Anderflash wrote:

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.

Control in Chart Sheet
 

Thanks for the clarification Jon.

Must admit I had not encountered this potential trap before.

Cheers
Andy

Jon Peltier wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy Pope" wrote in message
...

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 wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy Pope" wrote in message
. ..


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


Anderflash wrote:


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.

Control in Chart Sheet
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy Pope" wrote in message
...
Thanks for the clarification Jon.

Must admit I had not encountered this potential trap before.

Cheers
Andy

Jon Peltier wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy Pope" wrote in message
...

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 wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy Pope" wrote in message
...


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


Anderflash wrote:


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.