| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change in cell from formula to auto run a macro
Hi, I have been struggling with a problem for some time now, and I can't fin
my way around it. I have a cell f2 that has a formula, and I want Macro1 to run automatically when that value is different than 0, or Macro2 to run if that value is = 0. The thing is it detects manual changes (like tyoing the number myself), but it does nothing when the value changes using the formula. Any ideas, suggestions, etc.? Many thanks in advance. Alex |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change in cell from formula to auto run a macro
Private Sub Worksheet_Change(ByVal Target As Range)
if not intersect(target, range("H12:H1000")) is nothing if range("F2").value = 0 then Call Macro2 else Call Macro1 endif end if end sub -- HTH... Jim Thomlinson "Alex Martins" wrote: Hi, I have been struggling with a problem for some time now, and I can't fin my way around it. I have a cell f2 that has a formula, and I want Macro1 to run automatically when that value is different than 0, or Macro2 to run if that value is = 0. The thing is it detects manual changes (like tyoing the number myself), but it does nothing when the value changes using the formula. Any ideas, suggestions, etc.? Many thanks in advance. Alex |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change in cell from formula to auto run a macro
I trust that this works better than the last crack we took at this... It was
getting late and I was getting a little slow... -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if not intersect(target, range("H12:H1000")) is nothing if range("F2").value = 0 then Call Macro2 else Call Macro1 endif end if end sub -- HTH... Jim Thomlinson "Alex Martins" wrote: Hi, I have been struggling with a problem for some time now, and I can't fin my way around it. I have a cell f2 that has a formula, and I want Macro1 to run automatically when that value is different than 0, or Macro2 to run if that value is = 0. The thing is it detects manual changes (like tyoing the number myself), but it does nothing when the value changes using the formula. Any ideas, suggestions, etc.? Many thanks in advance. Alex |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change in cell from formula to auto run a macro
Hey Jim,. Thanks for answering again!
Here's what I got. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Worksheets("NEW FILINGS").Range("h12:h1000")) Is Nothing Then If Worksheets("NEW FILINGS").Range("h12:h1000").Value = 0 Then Call GetReported Else Call GetReports End If End If End Sub The problem is that (h12-h1000) are formulas as well...is this disrupting the propoer function of this code? Thanks in advance, Alex "Jim Thomlinson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if not intersect(target, range("H12:H1000")) is nothing if range("F2").value = 0 then Call Macro2 else Call Macro1 endif end if end sub -- HTH... Jim Thomlinson "Alex Martins" wrote: Hi, I have been struggling with a problem for some time now, and I can't fin my way around it. I have a cell f2 that has a formula, and I want Macro1 to run automatically when that value is different than 0, or Macro2 to run if that value is = 0. The thing is it detects manual changes (like tyoing the number myself), but it does nothing when the value changes using the formula. Any ideas, suggestions, etc.? Many thanks in advance. Alex |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change in cell from formula to auto run a macro
What are the formulas in H12 through H1000. We need to capture changes to the
precidents of these formulas. If there is no easy way to track all of the cells which can effect the values of H12-1000 then we will have to come at this from a new direction... -- HTH... Jim Thomlinson "Alex Martins" wrote: Hey Jim,. Thanks for answering again! Here's what I got. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Worksheets("NEW FILINGS").Range("h12:h1000")) Is Nothing Then If Worksheets("NEW FILINGS").Range("h12:h1000").Value = 0 Then Call GetReported Else Call GetReports End If End If End Sub The problem is that (h12-h1000) are formulas as well...is this disrupting the propoer function of this code? Thanks in advance, Alex "Jim Thomlinson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if not intersect(target, range("H12:H1000")) is nothing if range("F2").value = 0 then Call Macro2 else Call Macro1 endif end if end sub -- HTH... Jim Thomlinson "Alex Martins" wrote: Hi, I have been struggling with a problem for some time now, and I can't fin my way around it. I have a cell f2 that has a formula, and I want Macro1 to run automatically when that value is different than 0, or Macro2 to run if that value is = 0. The thing is it detects manual changes (like tyoing the number myself), but it does nothing when the value changes using the formula. Any ideas, suggestions, etc.? Many thanks in advance. Alex |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change in cell from formula to auto run a macro
Hey Jim, you hav no idea how you helped me the other day! Thanks for all your
help! really. Alex "Jim Thomlinson" wrote: I trust that this works better than the last crack we took at this... It was getting late and I was getting a little slow... -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if not intersect(target, range("H12:H1000")) is nothing if range("F2").value = 0 then Call Macro2 else Call Macro1 endif end if end sub -- HTH... Jim Thomlinson "Alex Martins" wrote: Hi, I have been struggling with a problem for some time now, and I can't fin my way around it. I have a cell f2 that has a formula, and I want Macro1 to run automatically when that value is different than 0, or Macro2 to run if that value is = 0. The thing is it detects manual changes (like tyoing the number myself), but it does nothing when the value changes using the formula. Any ideas, suggestions, etc.? Many thanks in advance. Alex |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change in cell from formula to auto run a macro
Frequently if worksheet_change used to trip and stops working when a formula
is used in place of a cell change, you may need to use a worksheet_calculate event instead. Give it a try, it can't hurt. -- Gary's Student "Jim Thomlinson" wrote: What are the formulas in H12 through H1000. We need to capture changes to the precidents of these formulas. If there is no easy way to track all of the cells which can effect the values of H12-1000 then we will have to come at this from a new direction... -- HTH... Jim Thomlinson "Alex Martins" wrote: Hey Jim,. Thanks for answering again! Here's what I got. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Worksheets("NEW FILINGS").Range("h12:h1000")) Is Nothing Then If Worksheets("NEW FILINGS").Range("h12:h1000").Value = 0 Then Call GetReported Else Call GetReports End If End If End Sub The problem is that (h12-h1000) are formulas as well...is this disrupting the propoer function of this code? Thanks in advance, Alex "Jim Thomlinson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if not intersect(target, range("H12:H1000")) is nothing if range("F2").value = 0 then Call Macro2 else Call Macro1 endif end if end sub -- HTH... Jim Thomlinson "Alex Martins" wrote: Hi, I have been struggling with a problem for some time now, and I can't fin my way around it. I have a cell f2 that has a formula, and I want Macro1 to run automatically when that value is different than 0, or Macro2 to run if that value is = 0. The thing is it detects manual changes (like tyoing the number myself), but it does nothing when the value changes using the formula. Any ideas, suggestions, etc.? Many thanks in advance. Alex |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change in cell from formula to auto run a macro
HEY JIM...WE GOT IT! I ADDED A SUB THAT PASTES VALUES THE REUSLT OF H1 TO
H1000 TO A CELL IN THE CELL INSIDE THE CODE YOU GAVE ME AND THAT DID IT! THIS WAY, EVERY TIME THE SUB RUNS IS MAKES AN EVENT CHANGE AND IT DETECTS. THANKS FOR EVERYTHING. HAVE GOOD NIGHT! YOU'VE BEEN MOST HELPFUL, REALLY. Alex "Jim Thomlinson" wrote: What are the formulas in H12 through H1000. We need to capture changes to the precidents of these formulas. If there is no easy way to track all of the cells which can effect the values of H12-1000 then we will have to come at this from a new direction... -- HTH... Jim Thomlinson "Alex Martins" wrote: Hey Jim,. Thanks for answering again! Here's what I got. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Worksheets("NEW FILINGS").Range("h12:h1000")) Is Nothing Then If Worksheets("NEW FILINGS").Range("h12:h1000").Value = 0 Then Call GetReported Else Call GetReports End If End If End Sub The problem is that (h12-h1000) are formulas as well...is this disrupting the propoer function of this code? Thanks in advance, Alex "Jim Thomlinson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if not intersect(target, range("H12:H1000")) is nothing if range("F2").value = 0 then Call Macro2 else Call Macro1 endif end if end sub -- HTH... Jim Thomlinson "Alex Martins" wrote: Hi, I have been struggling with a problem for some time now, and I can't fin my way around it. I have a cell f2 that has a formula, and I want Macro1 to run automatically when that value is different than 0, or Macro2 to run if that value is = 0. The thing is it detects manual changes (like tyoing the number myself), but it does nothing when the value changes using the formula. Any ideas, suggestions, etc.? Many thanks in advance. Alex |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change in cell from formula to auto run a macro
The problem with calculate is that it fires every time a calculation occures
anywhere which is kind of like using a shotgun to swat misquitoes. It is my last resort and it requires a bit more work to be efficient. -- HTH... Jim Thomlinson "Gary's Student" wrote: Frequently if worksheet_change used to trip and stops working when a formula is used in place of a cell change, you may need to use a worksheet_calculate event instead. Give it a try, it can't hurt. -- Gary's Student "Jim Thomlinson" wrote: What are the formulas in H12 through H1000. We need to capture changes to the precidents of these formulas. If there is no easy way to track all of the cells which can effect the values of H12-1000 then we will have to come at this from a new direction... -- HTH... Jim Thomlinson "Alex Martins" wrote: Hey Jim,. Thanks for answering again! Here's what I got. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Worksheets("NEW FILINGS").Range("h12:h1000")) Is Nothing Then If Worksheets("NEW FILINGS").Range("h12:h1000").Value = 0 Then Call GetReported Else Call GetReports End If End If End Sub The problem is that (h12-h1000) are formulas as well...is this disrupting the propoer function of this code? Thanks in advance, Alex "Jim Thomlinson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if not intersect(target, range("H12:H1000")) is nothing if range("F2").value = 0 then Call Macro2 else Call Macro1 endif end if end sub -- HTH... Jim Thomlinson "Alex Martins" wrote: Hi, I have been struggling with a problem for some time now, and I can't fin my way around it. I have a cell f2 that has a formula, and I want Macro1 to run automatically when that value is different than 0, or Macro2 to run if that value is = 0. The thing is it detects manual changes (like tyoing the number myself), but it does nothing when the value changes using the formula. Any ideas, suggestions, etc.? Many thanks in advance. Alex |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change in cell from formula to auto run a macro
Hey Jim. Let me tell you how I solved it. I added to one of the subs I call a
part where it copies a cell with a formula, and pastes values that number in the rane! This way it always detects the change in the screen! Thanks for all your help man. Alex "Alex Martins" wrote: HEY JIM...WE GOT IT! I ADDED A SUB THAT PASTES VALUES THE REUSLT OF H1 TO H1000 TO A CELL IN THE CELL INSIDE THE CODE YOU GAVE ME AND THAT DID IT! THIS WAY, EVERY TIME THE SUB RUNS IS MAKES AN EVENT CHANGE AND IT DETECTS. THANKS FOR EVERYTHING. HAVE GOOD NIGHT! YOU'VE BEEN MOST HELPFUL, REALLY. Alex "Jim Thomlinson" wrote: What are the formulas in H12 through H1000. We need to capture changes to the precidents of these formulas. If there is no easy way to track all of the cells which can effect the values of H12-1000 then we will have to come at this from a new direction... -- HTH... Jim Thomlinson "Alex Martins" wrote: Hey Jim,. Thanks for answering again! Here's what I got. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Worksheets("NEW FILINGS").Range("h12:h1000")) Is Nothing Then If Worksheets("NEW FILINGS").Range("h12:h1000").Value = 0 Then Call GetReported Else Call GetReports End If End If End Sub The problem is that (h12-h1000) are formulas as well...is this disrupting the propoer function of this code? Thanks in advance, Alex "Jim Thomlinson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if not intersect(target, range("H12:H1000")) is nothing if range("F2").value = 0 then Call Macro2 else Call Macro1 endif end if end sub -- HTH... Jim Thomlinson "Alex Martins" wrote: Hi, I have been struggling with a problem for some time now, and I can't fin my way around it. I have a cell f2 that has a formula, and I want Macro1 to run automatically when that value is different than 0, or Macro2 to run if that value is = 0. The thing is it detects manual changes (like tyoing the number myself), but it does nothing when the value changes using the formula. Any ideas, suggestions, etc.? Many thanks in advance. Alex |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change in cell from formula to auto run a macro
I'm new to this group, and this looks very similar to what I need. How would I modify this macro so that whenever data is typed into the workbook/worksheet, a macro to "unwrap text" automatically runs? Thanks! Beth -- johnandbeth ------------------------------------------------------------------------ johnandbeth's Profile: http://www.excelforum.com/member.php...o&userid=37911 View this thread: http://www.excelforum.com/showthread...hreadid=400984 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro - Auto - Sheet Name change ? expiry | Excel Discussion (Misc queries) | |||
| auto change font color in formula/cell | Excel Worksheet Functions | |||
| Autorun a macro on change of cell value (having formula) | Excel Worksheet Functions | |||
| is there a formula to auto change cell colours | Excel Discussion (Misc queries) | |||
| Auto Change Formula in different sheets | Excel Programming | |||
![Alex Martins[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
