Target Value Question

[Jenny B.]

Hello,

I have a Target Value macro set to run automatically and clear a cell if it
finds the zero value. It works but not quite the way I was hoping.

The Target Value is running on a page (“Front Pageâ€) that is based on a set
of cells linked from another page (“Data Pageâ€). Once “Data Page†is
cleared, a zero value appears on the “Front Pageâ€. Since this change is
brought about by the page value being removed vs. being entered, it does not
detect the change.

Is there something I could do that might bring about better results to
detect the value change?

Thank you – Jenny B.

 

[Mike H]

One way is to monitor the cells that could set your Front Page cell to Zero.
This macro could go in the Data Page and look at the cells that could set
front page A1 to zero. If one of those cells changes the value in front page
is checked and if it's zero then do something

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("d1:d10")) Is Nothing Then
If Sheets("Front Page").Range("a1").Value = 0 Then
'do something
MsgBox Sheets("Front Page").Range("a1").Value
End If
End If
End Sub

Mike

 

[JLatham]

I agree with Mike H - monitor the values that are manually entered (so you
can detect changes in them) and when a change in any of them occurs, then go
check your target cell for a value of zero and act accordingly.

 

[Jenny B.]

Hello,

Say I'm not trying to be thickheaded, but I'm not quite sure how the
application you wrote works. I forgot to provide my original code and I
apologize so maybe that's where I've created a bit of confusion.

The Range below is the area monitored on the "Front Page". Data is pasted
over to this workbook onto the "Data Page" and is then linked to the "Front
Page" ( Front Page linked to Data Page "B5" Cell). Since the Front Page
entry will not always be the same cell row due to the previous paste places
the contents in next empty cell, I'm not quite sure how apply your post to
get the needed results (looks like it's always set to a particular watch
cell).

Thanks for you continued advice - Jenny B.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Not Application.Intersect(Target, Range("L18:L33")) Is Nothing Then
If Target.Value = 0 Then
Target.Offset(0, 0).Select
Selection.ClearContents
End If
End If
End Sub