VBA to link cell on one sheet to cell on another sheet 
Author Message
 VBA to link cell on one sheet to cell on another sheet

In VBA, Excel 97.

This is going to sound trivial. I may have overlooked something obvious.

I'm trying to write a procedure to link a cell on one worksheet to a cell on
another worksheet. I've tried using the .Formula and .Value to do this, but
instead of creating a link, VBA inserts a string containing the link address
into the cell.

This is sample of the code I'm using;

wrk1.Range("B2").Formula = " =" & wrk2.Range("B2").Address

It perfectly formats the string to look like every other manually created
link on the worksheet, but it doesn't turn it into a formula. If I
substitute .Value for formula exactly the same thing happens.

Help! Please!

Keiran McManus
Cashtek.



Sun, 20 May 2001 03:00:00 GMT  
 VBA to link cell on one sheet to cell on another sheet
Keiran,

This has a little different sheet specification, but it will write the link:

wrk1.Range("B2").Formula = "=" & "sheet2" & "!B2"

HTH,

Brian

Quote:

> In VBA, Excel 97.

> This is going to sound trivial. I may have overlooked something obvious.

> I'm trying to write a procedure to link a cell on one worksheet to a cell on
> another worksheet. I've tried using the .Formula and .Value to do this, but
> instead of creating a link, VBA inserts a string containing the link address
> into the cell.

> This is sample of the code I'm using;

> wrk1.Range("B2").Formula = " =" & wrk2.Range("B2").Address

> It perfectly formats the string to look like every other manually created
> link on the worksheet, but it doesn't turn it into a formula. If I
> substitute .Value for formula exactly the same thing happens.

> Help! Please!

> Keiran McManus
> Cashtek.



Sun, 20 May 2001 03:00:00 GMT  
 VBA to link cell on one sheet to cell on another sheet
Hi Keiran,

Remove the space before the =, which is causing the entry to be text:

wrk1.Range("B2").Formula = " =" & wrk2.Range("B2").Address 'Wrong
wrk1.Range("B2").Formula = "=" & wrk2.Range("B2").Address  'Right

In the event that this is a typo, check to see that the B2 cell has not been
formatted as Text,

HTH,

John Green - Excel MVP
Sydney
Australia



Quote:

> Newsgroups: microsoft.public.excel.programming
> Subject: VBA to link cell on one sheet to cell on another sheet
> Date: Wed, 02 Dec 1998 22:57:24 GMT

> In VBA, Excel 97.

> This is going to sound trivial. I may have overlooked something obvious.

> I'm trying to write a procedure to link a cell on one worksheet to a cell on
> another worksheet. I've tried using the .Formula and .Value to do this, but
> instead of creating a link, VBA inserts a string containing the link address
> into the cell.

> This is sample of the code I'm using;

> wrk1.Range("B2").Formula = " =" & wrk2.Range("B2").Address

> It perfectly formats the string to look like every other manually created
> link on the worksheet, but it doesn't turn it into a formula. If I
> substitute .Value for formula exactly the same thing happens.

> Help! Please!

> Keiran McManus
> Cashtek.



Mon, 21 May 2001 03:00:00 GMT  
 VBA to link cell on one sheet to cell on another sheet
This is quite good, although it might be unintentional? The code below
places the CONTENT on cell B2 on wrk2 into cell B2 on wrk1. For example if
B2 (on wrk2) contains g12 then then formula pasted into cell B2 on wrk1
would be =g12
I didn't read the original question that way. I thought all Kieren needed
the code to do a direct reference
ie
wrk1.Range("B2").Formula = "=wrk2!B2"

However, this is useful code
Patrick Molloy
++++++++++++++++++++++++

Quote:

>Hi Keiran,

>Remove the space before the =, which is causing the entry to be text:

>wrk1.Range("B2").Formula = " =" & wrk2.Range("B2").Address 'Wrong
>wrk1.Range("B2").Formula = "=" & wrk2.Range("B2").Address  'Right

>In the event that this is a typo, check to see that the B2 cell has not
been
>formatted as Text,

>HTH,

>John Green - Excel MVP
>Sydney
>Australia




>> Newsgroups: microsoft.public.excel.programming
>> Subject: VBA to link cell on one sheet to cell on another sheet
>> Date: Wed, 02 Dec 1998 22:57:24 GMT

>> In VBA, Excel 97.

>> This is going to sound trivial. I may have overlooked something obvious.

>> I'm trying to write a procedure to link a cell on one worksheet to a cell
on
>> another worksheet. I've tried using the .Formula and .Value to do this,
but
>> instead of creating a link, VBA inserts a string containing the link
address
>> into the cell.

>> This is sample of the code I'm using;

>> wrk1.Range("B2").Formula = " =" & wrk2.Range("B2").Address

>> It perfectly formats the string to look like every other manually created
>> link on the worksheet, but it doesn't turn it into a formula. If I
>> substitute .Value for formula exactly the same thing happens.

>> Help! Please!

>> Keiran McManus
>> Cashtek.



Mon, 21 May 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Check if a cell on one sheet is referred to on another sheet

2. Distributing cell values from one Excel sheet to another Excel sheet on another computer - HOW TO

3. Howto: change a cell from one sheet to another sheet

4. Check if a cell on one sheet is referred to on another sheet

5. Distributing cell values from one Excel sheet to another Excel sheet on another computer - HOW TO

6. Check if a cell on one sheet is referred to on another sheet

7. Linking Sheet Tab Name to Cell in Current Sheet

8. XL97 problems copy/pasting entire sheet when cells link to other cells with comments

9. Cell reference within link to cell on another sheet

10. Copy Cell value from one table to another table different sheet-VBA Help


 
Powered by phpBB® Forum Software © phpBB Group