Run VBA code any time a particular range of cells is changed

AChimpNamedCornelius

Board Regular
Joined
Aug 22, 2002
Messages
91
Is there a way to cause a particular VB function I write to execute any time a value in a particular range of cells is changed?

I thought I had it when I thought I could do a sum formula, range on the cells to be checked, then call the function as a side effect of this sum. It was then that I realized I didn't know how to call VB from within an Excel cell formula.

1. Is there a way to call VB (say, sheet1.myFunction())?

2. Is there a better way to do what I want than to try to hack a side effect into a formula in a cell that depends on a sum (which "watches" for the changes for me.)


The cells would have text in them, not numbers, so using the sum-based formula would be iffy at runtime to me at best. What I really want to do is run a function on the cell value as a string. There are a lot of such cells (thousands) so ideally I would only need one external cell to activate the function (which scans all the cells I am concerned with) rather than have to paste a custom, relative formula into thousands of cells manually to have each one watch one cell, if you know what I mean.
This message was edited by AChimpNamedCornelius on 2002-08-23 13:09
 
I tried this method as well - here's the catch that got me, maybe it is why you can't "get it to work" either: I was brushing up on User Defined Functions in Excel 2002 VBA Programmer's Reference (Bullen, et all), and found out that UDF's cannot do ANY structural spreadsheet changes, nor can they call code that does (pg 28). Excel doesn't give an error message, it simply ignores the code. It apparently is only designed to return a numerical value to a cell, nothing more. Makes sense I guess - just wish Excel would have told me rather than ignore me.

This applies to 2002 and I assume to 2003 also since that is what I'm running. Don't know about 2007.
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top