Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help on triggering macro if cells change
Hi I have checked around but cannot seem to find out an answer for this. My worksheet has 24 cells where I am able to enter a number, and I want to be able to trigger a macro when a cell has a new number input. This I can do with Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Intersect(Target, Range("C31")) Is Nothing Then Exit Sub Else If True Then Run ("gettemp1") End If Application.ScreenUpdating = True End Sub This only works if you only have 1 cell to check on. How can I check on 24 different cells and each triggering a different macro? I thought I may have to add a command button after each cell but the user may forget to push it and not update the rest of the sheet. Thanks in advance. Warren -- cuewoz ------------------------------------------------------------------------ cuewoz's Profile: http://www.excelforum.com/member.php...o&userid=32161 View this thread: http://www.excelforum.com/showthread...hreadid=519139 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help on triggering macro if cells change
This will check a change in any of C31:F37
If Intersect(Target, Range("C31:F37")) Is Nothing Then This will check a change in any of C31:C37 or G31:G37 If Intersect(Target, Range("C31:C37, G31:G37")) Is Nothing Then -- HTH Bob Phillips (remove nothere from email address if mailing direct) "cuewoz" wrote in message ... Hi I have checked around but cannot seem to find out an answer for this. My worksheet has 24 cells where I am able to enter a number, and I want to be able to trigger a macro when a cell has a new number input. This I can do with Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Intersect(Target, Range("C31")) Is Nothing Then Exit Sub Else If True Then Run ("gettemp1") End If Application.ScreenUpdating = True End Sub This only works if you only have 1 cell to check on. How can I check on 24 different cells and each triggering a different macro? I thought I may have to add a command button after each cell but the user may forget to push it and not update the rest of the sheet. Thanks in advance. Warren -- cuewoz ------------------------------------------------------------------------ cuewoz's Profile: http://www.excelforum.com/member.php...o&userid=32161 View this thread: http://www.excelforum.com/showthread...hreadid=519139 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help on triggering macro if cells change
In addition to Bob's comments on testing target against a general range.
Once you have decided not to exit, you can test each cell in the range against target to decide what to do: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub Else If Not (Intersect(Target, Range("A1")) Is Nothing) Then MsgBox (Range("A1").Value) Else MsgBox (Range("B1").Value) End If End If Application.ScreenUpdating = True End Sub -- Gary''s Student "cuewoz" wrote: Hi I have checked around but cannot seem to find out an answer for this. My worksheet has 24 cells where I am able to enter a number, and I want to be able to trigger a macro when a cell has a new number input. This I can do with Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Intersect(Target, Range("C31")) Is Nothing Then Exit Sub Else If True Then Run ("gettemp1") End If Application.ScreenUpdating = True End Sub This only works if you only have 1 cell to check on. How can I check on 24 different cells and each triggering a different macro? I thought I may have to add a command button after each cell but the user may forget to push it and not update the rest of the sheet. Thanks in advance. Warren -- cuewoz ------------------------------------------------------------------------ cuewoz's Profile: http://www.excelforum.com/member.php...o&userid=32161 View this thread: http://www.excelforum.com/showthread...hreadid=519139 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help on triggering macro if cells change
select case Target.address
case "$A$1" do something case "$G$9" do something else ... end select HTH -- AP "cuewoz" a écrit dans le message de ... Hi I have checked around but cannot seem to find out an answer for this. My worksheet has 24 cells where I am able to enter a number, and I want to be able to trigger a macro when a cell has a new number input. This I can do with Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Intersect(Target, Range("C31")) Is Nothing Then Exit Sub Else If True Then Run ("gettemp1") End If Application.ScreenUpdating = True End Sub This only works if you only have 1 cell to check on. How can I check on 24 different cells and each triggering a different macro? I thought I may have to add a command button after each cell but the user may forget to push it and not update the rest of the sheet. Thanks in advance. Warren -- cuewoz ------------------------------------------------------------------------ cuewoz's Profile: http://www.excelforum.com/member.php...o&userid=32161 View this thread: http://www.excelforum.com/showthread...hreadid=519139 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help on triggering macro if cells change
If Intersect(Target, Range("C31").Resize(24,1)) Is Nothing Then
If they are not contiguous If intersect(Target,Range("C31,D9:D15,F11,G39,. . .,M3:M2")) is nothing then -- Regards, Tom Ogilvy "cuewoz" wrote in message ... Hi I have checked around but cannot seem to find out an answer for this. My worksheet has 24 cells where I am able to enter a number, and I want to be able to trigger a macro when a cell has a new number input. This I can do with Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Intersect(Target, Range("C31")) Is Nothing Then Exit Sub Else If True Then Run ("gettemp1") End If Application.ScreenUpdating = True End Sub This only works if you only have 1 cell to check on. How can I check on 24 different cells and each triggering a different macro? I thought I may have to add a command button after each cell but the user may forget to push it and not update the rest of the sheet. Thanks in advance. Warren -- cuewoz ------------------------------------------------------------------------ cuewoz's Profile: http://www.excelforum.com/member.php...o&userid=32161 View this thread: http://www.excelforum.com/showthread...hreadid=519139 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
help on triggering macro if cells change
Thanks for those useful replys They helped a lot! Unfortunatly they dont seem to work for cells that change but by way of a drop down menu changing a cell contents. any ideas? -- cuewoz ------------------------------------------------------------------------ cuewoz's Profile: http://www.excelforum.com/member.php...o&userid=32161 View this thread: http://www.excelforum.com/showthread...hreadid=519139 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
help on triggering macro if cells change
You must have Excel97.
Set another cell to refer to the DV cell (=D10 for instance), and test in a Worksheet_Calculate event. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "cuewoz" wrote in message ... Thanks for those useful replys They helped a lot! Unfortunatly they dont seem to work for cells that change but by way of a drop down menu changing a cell contents. any ideas? -- cuewoz ------------------------------------------------------------------------ cuewoz's Profile: http://www.excelforum.com/member.php...o&userid=32161 View this thread: http://www.excelforum.com/showthread...hreadid=519139 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Triggering a macro after Printing | Excel Programming | |||
Triggering a macro after Printing | Excel Programming | |||
Daily Macro Triggering | Excel Discussion (Misc queries) | |||
Triggering Macro Execution | Excel Programming | |||
auto-filter change not triggering worksheet_change event | Excel Programming |