Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call Macro when Cell within Range Changes
Hi All,
I understand how to set up a worksheet so that a macro is called when the value of a specific cell changes (i.e. when Cell A1 changes), but how do you call a macro when a cell within a range changes (a1:a10). I know this is simple but I can't find the right arguments? Any ideas? Ta Andi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call Macro when Cell within Range Changes
Andi,
Try the following: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then ' do something End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andibevan" wrote in message ... Hi All, I understand how to set up a worksheet so that a macro is called when the value of a specific cell changes (i.e. when Cell A1 changes), but how do you call a macro when a cell within a range changes (a1:a10). I know this is simple but I can't find the right arguments? Any ideas? Ta Andi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call Macro when Cell within Range Changes
Chip,
Thanks that's perfect. I am slightly confused as I have the following code which runs a macro when the cell range is selected whereas your macro works when the cell is changes. I can see what the difference is but don't understand why swapping the range and the target round makes such a difference. Regards Andi Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.Intersect(Range("A1:A10"), Target) Is Nothing Then 'Call your Macro Else End If End Sub "Chip Pearson" wrote in message ... Andi, Try the following: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then ' do something End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andibevan" wrote in message ... Hi All, I understand how to set up a worksheet so that a macro is called when the value of a specific cell changes (i.e. when Cell A1 changes), but how do you call a macro when a cell within a range changes (a1:a10). I know this is simple but I can't find the right arguments? Any ideas? Ta Andi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call Macro when Cell within Range Changes
Andi,
Yours is a SelectionChange event, so will run whenever a cell is selected, and call you macro if that selection is in A1:A10, whereas Chip's uses the Change event, so on ly runs when a cell is changed. -- HTH RP (remove nothere from the email address if mailing direct) "Andibevan" wrote in message ... Chip, Thanks that's perfect. I am slightly confused as I have the following code which runs a macro when the cell range is selected whereas your macro works when the cell is changes. I can see what the difference is but don't understand why swapping the range and the target round makes such a difference. Regards Andi Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.Intersect(Range("A1:A10"), Target) Is Nothing Then 'Call your Macro Else End If End Sub "Chip Pearson" wrote in message ... Andi, Try the following: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then ' do something End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andibevan" wrote in message ... Hi All, I understand how to set up a worksheet so that a macro is called when the value of a specific cell changes (i.e. when Cell A1 changes), but how do you call a macro when a cell within a range changes (a1:a10). I know this is simple but I can't find the right arguments? Any ideas? Ta Andi |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call Macro when Cell within Range Changes
Thanks Bob - The answer was staring me in the face. Easy when you know
how.. "Bob Phillips" wrote in message ... Andi, Yours is a SelectionChange event, so will run whenever a cell is selected, and call you macro if that selection is in A1:A10, whereas Chip's uses the Change event, so on ly runs when a cell is changed. -- HTH RP (remove nothere from the email address if mailing direct) "Andibevan" wrote in message ... Chip, Thanks that's perfect. I am slightly confused as I have the following code which runs a macro when the cell range is selected whereas your macro works when the cell is changes. I can see what the difference is but don't understand why swapping the range and the target round makes such a difference. Regards Andi Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.Intersect(Range("A1:A10"), Target) Is Nothing Then 'Call your Macro Else End If End Sub "Chip Pearson" wrote in message ... Andi, Try the following: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then ' do something End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andibevan" wrote in message ... Hi All, I understand how to set up a worksheet so that a macro is called when the value of a specific cell changes (i.e. when Cell A1 changes), but how do you call a macro when a cell within a range changes (a1:a10). I know this is simple but I can't find the right arguments? Any ideas? Ta Andi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
call range by cell value | Excel Worksheet Functions | |||
Passing a range in a macro call | Excel Programming | |||
Call a macro or sub based on the name of a cell? | Excel Programming | |||
Call a macro or sub based on the name of a cell? | Excel Programming |