how to update another cell on a value changing via code

TROOPER

New Member
Joined
Jan 25, 2005
Messages
3
Hi.
I have a cell containing an external link, eg stock value.

I wish to write a function that will update several cells (among other jobs) that is called when my original cell changes.

eg
cell a1 contains stock price.
cell a2 contains =myfunc(a1)

If myfunc returns a value that is put into A2, but I cannot make a call to
ActiveWorkbook.Sheets("SHEET1").range("c3").value = 10.

from within my function.

the funciton header is
Public Function myfunc(mcell As Integer) As Variant
This works when called with a fixed number, and I am sure I can get it to work when called with a cell ref, but I cannot update a third cell from this function. Is there any way?

Thanks
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Trooper,

Welcome to the board.

UDFs can only change the value of the cell that they are contained in, they cannot change the values in other cells.

Have you considered using a worksheet event routine to perform the actions that you require?
 

TROOPER

New Member
Joined
Jan 25, 2005
Messages
3
Thanks, The idea is to have a function run every time this value changes, eg to save share movements elsewhere, or monitor error conditions. Is is possible to trigger or post a call to another function (or class) to do this.

It is strange that if you run a function from the editor you can change any cell, yet if fired effectively as an event you cant.
The event functions on the worksheet I believe are not really granular enough, I want to fire an event from a specific cell change.


It seems counterintuative to not allow this.
I don't want to run a massive function every time any cell changes and I would rather not run a continuous loop. There has to be a simple way of doing this...
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi,

Well, I don't think UDFs are any different to the built-in functions in this regard. For example, if you entered this formula in B1 :

=IF(A1=123,A2=456,A3=789)

would you expect the cells in A2 or A3 to actually be changed?

As regards worksheet events, you can narrow the scope somewhat by checking the address of the cell changed. For example :
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$A$1" Then
        MsgBox "You changed A1"
    End If
End Sub
This will only fire when A1 is changed.

HTH
 

TROOPER

New Member
Joined
Jan 25, 2005
Messages
3
Understood, but if you write a function called off a button saying change 3 cells, that can be done, but if you want to do it after the change of an specific cell you can't. I will look at the worksheet event, but if you write a lot of code that will get huge and messy, even with individual calls.

Is there a way of referencing the previous value of a cell without it being considered a circular reference and causing problems, eg if trigger cell is 3, put now() into this cell if it is not blank?
 

Forum statistics

Threads
1,148,396
Messages
5,746,452
Members
424,020
Latest member
LongDoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top