Dear All,

I have an Excel report that shows Regional data.

I have designed a template that gets sent to the different Regions showing
their data only.

This data is sub-grouped into 11 different categories, the data to be
graphed depending on the Option Button selected.

When I copy the relevant sheets that make up the template into a new
workbook ( I group the required 4 sheets, then Copy to New Book), the Cell
Link reference follows the Option Button to the newly created workbook.

I wish to make sure that the 11 Option Buttons link to the same cell, but to
the new workbook, not the original.

ie.. from:

Cell Link: '[OriginalBook.xls]Sheet1'!$A$1

to:

Cell Link: Sheet1!$A$1

My guess is that some coding will be required, hence my posting in this
newsgroup.

Appreciate your help...


andy m

After you copy the pages, go into Edit=Links and change the link to the
original workbook to point at the current workbook.

--
regards,
Tom Ogilvy

"andym" wrote in message
...
Dear All,

I have an Excel report that shows Regional data.

I have designed a template that gets sent to the different Regions showing
their data only.

This data is sub-grouped into 11 different categories, the data to be
graphed depending on the Option Button selected.

When I copy the relevant sheets that make up the template into a new
workbook ( I group the required 4 sheets, then Copy to New Book), the Cell
Link reference follows the Option Button to the newly created workbook.

I wish to make sure that the 11 Option Buttons link to the same cell, but
to
the new workbook, not the original.

ie.. from:

Cell Link: '[OriginalBook.xls]Sheet1'!$A$1

to:

Cell Link: Sheet1!$A$1

My guess is that some coding will be required, hence my posting in this
newsgroup.

Appreciate your help...


andy m

Tom,

thanks for your reply.

As the creation of the new workbook is automated, I don't feel confident
that the users will either want to, or know how to continuously change the
link in a manual fashion.

I would prefer for the link to be updated at the time the new workbook is
created. Possibly the LinkedCell property may do the trick.

Thanks very much for taking the time to answer my query.

Regards,

andy m




"Tom Ogilvy" wrote in message
...
After you copy the pages, go into Edit=Links and change the link to the
original workbook to point at the current workbook.

--
regards,
Tom Ogilvy

"andym" wrote in message
...
Dear All,

I have an Excel report that shows Regional data.

I have designed a template that gets sent to the different Regions
showing
their data only.

This data is sub-grouped into 11 different categories, the data to be
graphed depending on the Option Button selected.

When I copy the relevant sheets that make up the template into a new
workbook ( I group the required 4 sheets, then Copy to New Book), the
Cell
Link reference follows the Option Button to the newly created workbook.

I wish to make sure that the 11 Option Buttons link to the same cell,
but
to
the new workbook, not the original.

ie.. from:

Cell Link: '[OriginalBook.xls]Sheet1'!$A$1

to:

Cell Link: Sheet1!$A$1

My guess is that some coding will be required, hence my posting in this
newsgroup.

Appreciate your help...


andy m

Tom,

I have just found a post you made in July of this year.

I have tried the following based upon that post...
With ActiveWorkbook.Worksheets("Template").OleObjects(" OptionButton1")
.LinkedCell = "Template!$A$1"
End With

However, I get the following message....

+++ Run-Time error '1004' ... Unable to get the OLEObjects property of the
Worksheets class. +++

I am running Excel 97 (!!).

Any ideas on how I can rectify this??

Once again, thanks for your time.

Regards,

andy m













"andym" wrote in message
...
Tom,

thanks for your reply.

As the creation of the new workbook is automated, I don't feel confident
that the users will either want to, or know how to continuously change the
link in a manual fashion.

I would prefer for the link to be updated at the time the new workbook is
created. Possibly the LinkedCell property may do the trick.

Thanks very much for taking the time to answer my query.

Regards,

andy m




"Tom Ogilvy" wrote in message
...
After you copy the pages, go into Edit=Links and change the link to the
original workbook to point at the current workbook.

--
regards,
Tom Ogilvy

"andym" wrote in message
...
Dear All,

I have an Excel report that shows Regional data.

I have designed a template that gets sent to the different Regions
showing
their data only.

This data is sub-grouped into 11 different categories, the data to be
graphed depending on the Option Button selected.

When I copy the relevant sheets that make up the template into a new
workbook ( I group the required 4 sheets, then Copy to New Book), the
Cell
Link reference follows the Option Button to the newly created
workbook.

I wish to make sure that the 11 Option Buttons link to the same cell,
but
to
the new workbook, not the original.

ie.. from:

Cell Link: '[OriginalBook.xls]Sheet1'!$A$1

to:

Cell Link: Sheet1!$A$1

My guess is that some coding will be required, hence my posting in
this
newsgroup.

Appreciate your help...


andy m

OleObjects would include optionbuttons from the Control Toolbox Toolbar.
However, you said optionbuttons linked to the same cell, so this is more
indicative of OptionButtons from the Forms toolbar.

In that case

for each Optbtn in ActiveWorkbook.Worksheets("Template") _
.OptionButtons
Optbtn.LinkedCell = "Template!$A$1"
Next


If you have the optionbuttons in a group box, then you only need to change
the assignment for 1 and all will change

ActiveWorkbook.Worksheets("Template") .OptionButtons(1).LinkedCell =
"Template!$A$1"

--
Regards,
Tom Ogilvy


"andym" wrote in message
...
Tom,

I have just found a post you made in July of this year.

I have tried the following based upon that post...
With ActiveWorkbook.Worksheets("Template").OleObjects(" OptionButton1")
.LinkedCell = "Template!$A$1"
End With

However, I get the following message....

+++ Run-Time error '1004' ... Unable to get the OLEObjects property of the
Worksheets class. +++

I am running Excel 97 (!!).

Any ideas on how I can rectify this??

Once again, thanks for your time.

Regards,

andy m













"andym" wrote in message
...
Tom,

thanks for your reply.

As the creation of the new workbook is automated, I don't feel confident
that the users will either want to, or know how to continuously change
the
link in a manual fashion.

I would prefer for the link to be updated at the time the new workbook
is
created. Possibly the LinkedCell property may do the trick.

Thanks very much for taking the time to answer my query.

Regards,

andy m




"Tom Ogilvy" wrote in message
...
After you copy the pages, go into Edit=Links and change the link to
the
original workbook to point at the current workbook.

--
regards,
Tom Ogilvy

"andym" wrote in message
...
Dear All,

I have an Excel report that shows Regional data.

I have designed a template that gets sent to the different Regions
showing
their data only.

This data is sub-grouped into 11 different categories, the data to
be
graphed depending on the Option Button selected.

When I copy the relevant sheets that make up the template into a new
workbook ( I group the required 4 sheets, then Copy to New Book),
the
Cell
Link reference follows the Option Button to the newly created
workbook.

I wish to make sure that the 11 Option Buttons link to the same
cell,
but
to
the new workbook, not the original.

ie.. from:

Cell Link: '[OriginalBook.xls]Sheet1'!$A$1

to:

Cell Link: Sheet1!$A$1

My guess is that some coding will be required, hence my posting in
this
newsgroup.

Appreciate your help...


andy m

Chip Pearson has written code that will support this. See his site:
http://www.cpearson.com/excel/imptext.htm

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
Dear All,

I have an Excel report that shows Regional data.

I have designed a template that gets sent to the different Regions showing
their data only.

This data is sub-grouped into 11 different categories, the data to be
graphed depending on the Option Button selected.

When I copy the relevant sheets that make up the template into a new
workbook ( I group the required 4 sheets, then Copy to New Book), the Cell
Link reference follows the Option Button to the newly created workbook.

I wish to make sure that the 11 Option Buttons link to the same cell, but
to
the new workbook, not the original.

ie.. from:

Cell Link: '[OriginalBook.xls]Sheet1'!$A$1

to:

Cell Link: Sheet1!$A$1

My guess is that some coding will be required, hence my posting in this
newsgroup.

Appreciate your help...


andy m

Tom,

I'm not too sure if the below was meant for my post. I checked this link and
couldn't find any reference to my post. I may have missed something ... It
wouldn't be the first time :) !!!!!

Regards,

andy m

Chip Pearson has written code that will support this. See his site:
http://www.cpearson.com/excel/imptext.htm

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
Dear All,

I have an Excel report that shows Regional data.

I have designed a template that gets sent to the different Regions
showing
their data only.

This data is sub-grouped into 11 different categories, the data to be
graphed depending on the Option Button selected.

When I copy the relevant sheets that make up the template into a new
workbook ( I group the required 4 sheets, then Copy to New Book), the
Cell
Link reference follows the Option Button to the newly created workbook.

I wish to make sure that the 11 Option Buttons link to the same cell,
but
to
the new workbook, not the original.

ie.. from:

Cell Link: '[OriginalBook.xls]Sheet1'!$A$1

to:

Cell Link: Sheet1!$A$1

My guess is that some coding will be required, hence my posting in this
newsgroup.

Appreciate your help...


andy m

Looks like I posted that in the wrong thread. Sorry for that.

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
Tom,

I'm not too sure if the below was meant for my post. I checked this link
and
couldn't find any reference to my post. I may have missed something ... It
wouldn't be the first time :) !!!!!

Regards,

andy m

Chip Pearson has written code that will support this. See his site:
http://www.cpearson.com/excel/imptext.htm

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
Dear All,

I have an Excel report that shows Regional data.

I have designed a template that gets sent to the different Regions
showing
their data only.

This data is sub-grouped into 11 different categories, the data to be
graphed depending on the Option Button selected.

When I copy the relevant sheets that make up the template into a new
workbook ( I group the required 4 sheets, then Copy to New Book), the
Cell
Link reference follows the Option Button to the newly created
workbook.

I wish to make sure that the 11 Option Buttons link to the same cell,
but
to
the new workbook, not the original.

ie.. from:

Cell Link: '[OriginalBook.xls]Sheet1'!$A$1

to:

Cell Link: Sheet1!$A$1

My guess is that some coding will be required, hence my posting in
this
newsgroup.

Appreciate your help...


andy m