worksheet_Change with Formulas

I'm new to this; hopefully someone may be able to help. I have a spreadsheet
were I want to track cells that change. So, when any value within A2:A10 or
B2:B10 changes, I would like to put an "x" within the applicable row in
Column F. I've been able to find a solution to part of my issue by searching
through other questions and answers and have been able to alter the code so
that it works with values that are user-entered, A2:A10. Unfortunately
though, I've been unsuccessful when referring to cells containing formulas,
B2:B10. I've added some sample code below. In this example, values in Range
B2:B10 are populated from values within
another worksheet contained within the same workbook. Any help is much
appreciated.
Thanks, Mike


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.Value = "x"
End With
End If
Application.EnableEvents = True
End If
End With
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("B2:B10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 4).ClearContents
Else
With .Offset(0, 4)
.Value = "x"
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Hi Mike,

I think you have two issues. First, if I understand the issue correctly, the
cells in column B have formulae referring to another sheet. Consequently,
those cells will never be 'empty'. I think you really want to check if the
cells, i.e. the result of the formula / cell reference, is zero or not a
blank text string, depending on the result of the formula. Second, if those
are formula in column B, then a change in the result of the formula will not
trigger the Worksheet_Change event.

The following is a modification of your code that I think will do the trick
for you.

Private Sub Worksheet_Calculate()
Dim currCell As Range
For Each currCell In Range("B2:B10")
With currCell
Application.EnableEvents = False
'If I understand you correctly, IsEmpty(.Value) will always be
'false as these cells contain formulae. I think you want to
'check if .Text = "" or .Value = 0, depending on the nature
'of the data in the other worksheet.
If .Value = 0 Then
.Offset(0, 4).ClearContents
Else
With .Offset(0, 4)
.Value = "x"
End With
End If
Application.EnableEvents = True
End With
Next currCell
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.Value = "x"
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

--
Timothy White
Contract Programmer
Ontario, Canada

<my initials>hite<at>sympatico<dot><countryCode>



"Mike G - D.C." wrote:

> I'm new to this; hopefully someone may be able to help. I have a spreadsheet ...
~~~~~
snip
~~~~~

Hi Mike,

I think you have two issues. First, if I understand the issue correctly, the
cells in column B have formulae referring to another sheet. Consequently,
those cells will never be 'empty'. I think you really want to check if the
cells, i.e. the result of the formula / cell reference, is zero or not a
blank text string, depending on the result of the formula. Second, if those
are formula in column B, then a change in the result of the formula will not
trigger the Worksheet_Change event.

The following is a modification of your code that I think will do the trick
for you.

Private Sub Worksheet_Calculate()
Dim currCell As Range
For Each currCell In Range("B2:B10")
With currCell
Application.EnableEvents = False
'If I understand you correctly, IsEmpty(.Value) will always be
'false as these cells contain formulae. I think you want to
'check if .Text = "" or .Value = 0, depending on the nature
'of the data in the other worksheet.
If .Value = 0 Then
.Offset(0, 4).ClearContents
Else
With .Offset(0, 4)
.Value = "x"
End With
End If
Application.EnableEvents = True
End With
Next currCell
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.Value = "x"
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

--
Timothy White
Contract Programmer
Ontario, Canada

<my initials>hite<at>sympatico<dot><countryCode>



"Mike G - D.C." wrote:

> I'm new to this; hopefully someone may be able to help. I have a spreadsheet ...
~~~~~
snip
~~~~~

Hi Mike,

I think you have two issues. First, if I understand the issue correctly, the
cells in column B have formulae referring to another sheet. Consequently,
those cells will never be 'empty'. I think you really want to check if the
cells, i.e. the result of the formula / cell reference, is zero or not a
blank text string, depending on the result of the formula. Second, if those
are formula in column B, then a change in the result of the formula will not
trigger the Worksheet_Change event.

The following is a modification of your code that I think will do the trick
for you.

Private Sub Worksheet_Calculate()
Dim currCell As Range
For Each currCell In Range("B2:B10")
With currCell
Application.EnableEvents = False
'If I understand you correctly, IsEmpty(.Value) will always be
'false as these cells contain formulae. I think you want to
'check if .Text = "" or .Value = 0, depending on the nature
'of the data in the other worksheet.
If .Value = 0 Then
.Offset(0, 4).ClearContents
Else
With .Offset(0, 4)
.Value = "x"
End With
End If
Application.EnableEvents = True
End With
Next currCell
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.Value = "x"
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

--
Timothy White
Contract Programmer
Ontario, Canada

<my initials>hite<at>sympatico<dot><countryCode>



"Mike G - D.C." wrote:

> I'm new to this; hopefully someone may be able to help. I have a spreadsheet ...
~~~~~
snip
~~~~~

TDW -
I appreciate the response. I've added your code to my worksheet and I
believe the fix is on the right track. To answer your question, yes, one of
the main issues with this scenario is that changes in formula results,
contained within column B, will not trigger the worksheet change event
referenced within my sample code. I need the same worksheet change behavior
established for range A2:A10 to apply to the results within B2:B10.

The code provided will add an "x" within column F for every new value that
is introduced, either manually, or via formula result. However, within my
initial description, I neglected to include that at some point, probably
monthly; I'll go in and delete all of the x's from column F. The idea is that
when a particular value changes within A2:A10 and B2:B10, an x is added to
the applicable row designating that a value has changed since last month.
Currently, the code seems to add an x in column F for every value contained
within B2:B10 and doesn’t seem to distinguish whether or not the value has
changed. Is there any way that I can achieve this using the solution that I
initially pieced together, or is there another approach that would be more
efficient?
Thanks, Mike


"tdw" wrote:

> Hi Mike,
>
> I think you have two issues. First, if I understand the issue correctly, the
> cells in column B have formulae referring to another sheet. Consequently,
> those cells will never be 'empty'. I think you really want to check if the
> cells, i.e. the result of the formula / cell reference, is zero or not a
> blank text string, depending on the result of the formula. Second, if those
> are formula in column B, then a change in the result of the formula will not
> trigger the Worksheet_Change event.
>
> The following is a modification of your code that I think will do the trick
> for you.
>
> Private Sub Worksheet_Calculate()
> Dim currCell As Range
> For Each currCell In Range("B2:B10")
> With currCell
> Application.EnableEvents = False
> 'If I understand you correctly, IsEmpty(.Value) will always be
> 'false as these cells contain formulae. I think you want to
> 'check if .Text = "" or .Value = 0, depending on the nature
> 'of the data in the other worksheet.
> If .Value = 0 Then
> .Offset(0, 4).ClearContents
> Else
> With .Offset(0, 4)
> .Value = "x"
> End With
> End If
> Application.EnableEvents = True
> End With
> Next currCell
> End Sub
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> With Target
> If .Count > 1 Then Exit Sub
> If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
> Application.EnableEvents = False
> If IsEmpty(.Value) Then
> .Offset(0, 5).ClearContents
> Else
> With .Offset(0, 5)
> .Value = "x"
> End With
> End If
> Application.EnableEvents = True
> End If
> End With
> End Sub
>
> --
> Timothy White
> Contract Programmer
> Ontario, Canada
>
> <my initials>hite<at>sympatico<dot><countryCode>
>
>
>
> "Mike G - D.C." wrote:
>
> > I'm new to this; hopefully someone may be able to help. I have a spreadsheet ...
> ~~~~~
> snip
> ~~~~~

Mike,

So, you want to know if the results of the formulae in column B have
changed, not simply if there's a value as indicated by your use of the
IsEmpty() function?

Assuming you have a complex formula in column B, i.e. one that references
multiple cells or values, then you would have to keep track of what the
previous value was in order to know if it has changed. (Anyone else reading
this, please feel free to correct me if you know of an easier way.) You would
then need to compare the current contents to the previous contents. This is
getting a little more complicated.

On the other hand, assuming your formula in column B are simply references
to cells on another sheet (i.e. the formula in B2 reads
"=SomeSheet!$Col$Row", without the quotes) then I think you could simply trap
the _Change event on the SomeSheet worksheet to update the original sheet.

Let me know if you have more questions or would like some clarification.
Feel free to contact me directly using the e-mail address listed in my
signature below.

HTH,
tdw


--
Timothy White
Contract Programmer
Ontario, Canada

<my initials>hite<at>sympatico<dot><countryCode>