Triggering Macro Execution

[Peter M]

I want to run a macro that detects when a particular cell in a Range changes
and then review that range and resolve conflicts that may exist. I think I
have two possible problems:

1. How to trigger the macro when any cell in the range changes.

2. If the answer to 1 is to define a Function to which the range is an
input, how do I get it to change a cell without actually returning a value?
For example:

Function test(tt As Range)

Range("F17").Select
ActiveCell.FormulaR1C1 = "try here"

End Function

This function is called whenever a cell in the 'tt' range is change, but
when the line: ActiveCell.FormulaR1C1 = "try here"
is executed the function fails with a #value error.

Any ideas gratefully received.

 

[Bob Phillips]

I want to run a macro that detects when a particular cell in a Range changes
and then review that range and resolve conflicts that may exist. I think I
have two possible problems:

1. How to trigger the macro when any cell in the range changes.

Use the Worksheet_Change event. Here is a simple example that tests whether
the changed cell is within the range A1:A10

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
DoSomething Target 'call a proc called DoSomething wih the
changed cell as argument
End If

ws_exit:
Application.EnableEvents = True

End Sub

2. If the answer to 1 is to define a Function to which the range is an
input, how do I get it to change a cell without actually returning a

value?

You don't need to call a function, it can be a simple procedure, or embedded
in the event code. If you do use a function, you could simply return True or
False to indicate success or failure.

For example:

Function test(tt As Range)

Range("F17").Select
ActiveCell.FormulaR1C1 = "try here"

End Function

This function is called whenever a cell in the 'tt' range is change, but
when the line: ActiveCell.FormulaR1C1 = "try here"
is executed the function fails with a #value error.

Don 't know why you get an error, but it is not necessary to use FormulaR1C1
to set a value, simply use

Range("F17").Value = "try here"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)