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

[AChimpNamedCornelius]

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

 

[lenze]

Use the worksheet change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
DoMyCode
End Sub

If your target range includes more than one cell, repost with info and we can modify the code

 

[Russell Hauf]

Try searching on Worksheet_SelectionChange - you should get more than enough information to do what you want. If you can't find what you need, feel free to repost or, as a last resort, email me (but do so before 4pm PDT).

-rh

 

[phantom1975]

You could put your macro within the Worksheet_SelectionChange(ByVal Target As Excel.Range) event. This will monitor any changes that are done within the entire worksheet, but you can customize the macro to only do something when your selected range has a change in it.

 

[AChimpNamedCornelius]

> Try searching on Worksheet_SelectionChange

Yes, 940 pages of things.

I do try to search for answers, really I do.

For example, searching for "call visual basic from command line" yields a similar useless number of hits. There is no button on how to use search, either, as (with double quotes to make it a phrase) "command line" yields none.

The command line question: For use in a build script, how would I call, say, Sheet1.myFunction("Hello, world!") from a command line?

Intuitively it might be something like:

<Excel path> Excel.exe <my workbook path> -VBAexecute"Sheet1.myFunction("Hello, world!")

 

[BingoBingo]

It might not be much help now, but maybe someone searching through the forums will find it useful.

This is what I have done in the past to solve your problem:

function CallMeWhenSomeCellsChange(CellsThatChange)
  sheet1.myfunction()
end function

Put the function somewhere in your spreadsheet, in a cell like this:

=CallMeWhenSomeCellsChange(A1:A4)

It doesn't matter what cell you put the above line in, and it will call your code when cells A1,A2,A3 or A4 change.

-B

 

[stilllearning22]

BingoBingo,

What I'm trying to do is to get text in cells to change colour when overtyped. I've tried to use your code, but can't get it to work as I don't understand. Could you please show me what I would need to use as myfunction()? Also, how does your function know to call the macro when the cell is changed?

Cheers

 

[BingoBingo]

Hello Mr. Still learning,

You probably should have started a new thread for this. It is a slightly different problem.

My code uses the fact that Excel will recalculate any formulas whos inputs have changed. So when the inputs to the function change, the function is rerun and it calls some other code. The cell that the function is in may or may not be a "dud" cell with no useful information.

My method does not seem useful to you for two reasons:
In the code, the function does not know what cell it is being called from.. only what cells have changed.
If you overwrite the formula, no code will be called.

You should probably try and use the change event for the worksheet as discussed earlier.

It is hard to help with so little details. Are you trying to colour overwrites generally across the whole sheet, or just for a specific range? Do these have default values that are known, or is it more general? Do you protect the sheet, thus allowing hidden columns (with some sort of security) storing the original values for comparison?

There are a number of design paths. I doubt the absolute general case is possible.

-B

 

[BingoBingo]

Hello again,

Here is the example I found in the Microsoft Visual Basic Help:

Private Sub Worksheet_Change(ByVal Target as Range)
    Target.Font.ColorIndex = 5
End Sub

:D
-B

 

[stilllearning22]

Sorry I didn't realise it was so different.

I've tried your code but nothing happens just yet, do I need to add anything anywhere else?

My problem is I want to show a spreadsheet with numbers in a selected range in a grey font. When each numbers is overtyped, I want it to change the font colour to black, even if the number is the same as it was before. This is therefore a mechanism for seeing changes made.

Thanks