Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Target Value Question
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Target Value Question
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 "Jenny B." wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Target Value Question
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." wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Target Value Question
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 "Mike H" wrote: 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 "Jenny B." wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
target.value | Excel Discussion (Misc queries) | |||
Target Question | New Users to Excel | |||
Target Question | New Users to Excel | |||
Target | Excel Worksheet Functions |