Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
Mallick wrote:
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
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 -- Steve "Mallick" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
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 -- Steve "AltaEgo" <Somewhere@NotHere wrote in message ... 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 -- Steve "Mallick" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
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 "AltaEgo" wrote: 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 -- Steve "Mallick" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
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" wrote: 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 "AltaEgo" wrote: 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 -- Steve "Mallick" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
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 "AltaEgo" wrote: 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 -- Steve "Mallick" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
Try Worksheet_Calculate event.
Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 03:14:02 -0700, Mallick wrote: 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 "AltaEgo" wrote: 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 -- Steve "Mallick" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
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/usin...reate-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. -- Steve "Mallick" wrote in message ... 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 "AltaEgo" wrote: 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 -- Steve "Mallick" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
Thanks Steve for your elaborate answer
"AltaEgo" wrote: 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/usin...reate-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. -- Steve "Mallick" wrote in message ... 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 "AltaEgo" wrote: 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 -- Steve "Mallick" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
My pleasure. Thank you for the feedback.
-- Steve "Mallick" wrote in message ... Thanks Steve for your elaborate answer "AltaEgo" wrote: 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/usin...reate-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. -- Steve "Mallick" wrote in message ... 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 "AltaEgo" wrote: 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 -- Steve "Mallick" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro when a value in a range of cell changes
Nops, Gord
It isnt working............ "Gord Dibben" wrote: Try Worksheet_Calculate event. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 03:14:02 -0700, Mallick wrote: 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 "AltaEgo" wrote: 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 -- Steve "Mallick" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running a macro when a cell in a range has changed | Excel Programming | |||
How do I call up a line of code that references a cell/range in theactive workbook workbook where I am running my macro from? | Excel Programming | |||
Create macro to calculate data range for a running chart | Excel Worksheet Functions | |||
Running a macro in a predetermine range if not them msgBox | Excel Programming | |||
Running a macro if any data is entered in a range of cells | Excel Worksheet Functions |