Running a Macro when a value in a range of cell changes

[Mallick]

Hi
I am trying to run my macro whenever there is change of values in a range of
cells. For example, A table of 4x4 contains all zeros initially, I want the
macro to run whenever the initial value in any cell changes.

Can anyone help me with this?

Many Thanks.

Mallick

 

[smartin]

Hi
I am trying to run my macro whenever there is change of values in a range of
cells. For example, A table of 4x4 contains all zeros initially, I want the
macro to run whenever the initial value in any cell changes.

Can anyone help me with this?

Many Thanks.

Mallick

I dug up this VBA code that might help. This sub was designed to handle
a click in a cell that contained a fake hyperlink that would
nevertheless transport the user to a chart sheet (actual hyperlinks
cannot point to chart sheets). I think you could easily adapt this to
capture a Worksheet_Change event.

The salient logic is on line 3, where you process where the change
occurred to see if it is in the range you are concerned about. In the
example I was looking for a click anywhere in column B.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' fakey hyperlink
If Not Intersect(Target, Range("B" & Target.Row)) Is Nothing Then
On Error Resume Next
Charts(Target.Value).Activate
On Error GoTo 0
End If
End Sub


PS I know I stole this idea from somebody, but I didn't attribute the
source... very unlike me... tsk! If this looks like your code, please
let me know.

 

[AltaEgo]

Hi Mallick

The following should do the trick.

1) Right click the tab of the relevant worksheet
2) Click "View Code"
3) Paste
4) You also need to specify the range of cells where the code should
activate. The easiest way to do this is to use a named range. In the sample
below, I named my cells myRange.


Private Sub Worksheet_Change(ByVal Target As Range)

Set isect = Application.Intersect(Target, Range("myRange"))
If Not isect Is Nothing Then
'replace the next line with your code
' or call your macro
MsgBox Target
msgbox Target.address

End If

End Sub

 

[AltaEgo]

You also need to be able to delete data from more than one cell without
producing an error:

Private Sub Worksheet_Change(ByVal Target As Range)
If Selection.Count = 1 Then
Set isect = Application.Intersect(Target, Range("myRange"))
If Not isect Is Nothing Then
'message boxes show how to return the new value
' of the cell address
'replace the msgbox lines with your code
' or call your macro
MsgBox Target
MsgBox Target.Address

End If
End If

End Sub

 

[Mallick]

Thanks smartin and AltaEgo for your quick replies.

AltaEgo, Your step wise answer was easy to implement but can you please
explain intersect method in general and what specific task it performs here.

Many Thanks

Mallick

 

[Mallick]

Hi

would a following simpler approach do the trick for me?

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
Call Main
End Sub

Many Thanks

Mallick

 

[Mallick]

Hi Steve

Thanks for your help. It solves the problem that I put in my question.
However, there is still a catch.

If i manually change my values in "myRange", it performs perfectly. However,
my real issue was that "myRange" actually consists of decision variables of
an optimization problem. I am using Pallisade Add-In called Evolver. Evolver
changes the decision variables in each iteration and I want my macro to run
each time the decision variable changes.

Do you have any solution to this problem.

Many thanks.

Mallick

 

[Gord Dibben]

Try Worksheet_Calculate event.


Gord Dibben MS Excel MVP

 

[AltaEgo]

Mallick,

Straight from XL2003 Help on intersect:

Returns a Range object that represents the rectangular intersection of two
or more ranges.

Step through the code on this to see what Intersect actually returns:
http://www.excely.com/excel-vba/using-intersection-to-create-a-range/


In the code below, Target is the cell that just changed. So,
Application.Intersect(Target, Range("myRange")) is checking whether the just
changed cell falls within the named range, myrange. If the cells intersect,
it returns and address. If not, it doesn't return anything. The double
negatives are a bit of a brain-stretcher. Think of "If Not isect Is Nothing"
as 'if isect has a value'. Alternatively, you could use the following
method:

If isect Is Nothing Then
'do nothing
Else
'code if ranges intersect
End IF


You don't need to use a named range (e.g. you could use
Application.Intersect(Target, Range("A1:B16")). However, a named range
offers advantages. The main advantage being if the target address range
changes, you don't need to open your VBA module and search for relevant
pieces of code to alter. You just need to update the named range. Taking it
to the next step, if you use a dynamic named range, you don't need to do
anything.

If my explanation was clumsy:
http://www.ozgrid.com/VBA/vba-intersect.htm

I trust Gord solved the balance of your problem.

 

[Mallick]

Thanks Steve for your elaborate answer

Mallick,

Straight from XL2003 Help on intersect:

Returns a Range object that represents the rectangular intersection of two
or more ranges.

Step through the code on this to see what Intersect actually returns:
http://www.excely.com/excel-vba/using-intersection-to-create-a-range/


In the code below, Target is the cell that just changed. So,
Application.Intersect(Target, Range("myRange")) is checking whether the just
changed cell falls within the named range, myrange. If the cells intersect,
it returns and address. If not, it doesn't return anything. The double
negatives are a bit of a brain-stretcher. Think of "If Not isect Is Nothing"
as 'if isect has a value'. Alternatively, you could use the following
method:

If isect Is Nothing Then
'do nothing
Else
'code if ranges intersect
End IF


You don't need to use a named range (e.g. you could use
Application.Intersect(Target, Range("A1:B16")). However, a named range
offers advantages. The main advantage being if the target address range
changes, you don't need to open your VBA module and search for relevant
pieces of code to alter. You just need to update the named range. Taking it
to the next step, if you use a dynamic named range, you don't need to do
anything.

If my explanation was clumsy:
http://www.ozgrid.com/VBA/vba-intersect.htm

I trust Gord solved the balance of your problem.

 

[AltaEgo]

My pleasure. Thank you for the feedback.

 

[Mallick]

Nops, Gord

It isnt working............

Try Worksheet_Calculate event.


Gord Dibben MS Excel MVP