Option Button->Format Control->Cell Link...

[andym]

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 Ogilvy]

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

 

[Tom Ogilvy]

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

 

[andym]

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

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

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

 

[andym]

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

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

 

[andym]

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

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

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

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 Ogilvy]

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

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

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

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

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 Ogilvy]

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

--
Regards,
Tom Ogilvy

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

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