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

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

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

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

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

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

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

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

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

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

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