Follow up for Mike H or J Latham

[Jenny B.]

Hello,

You both had replied to my earlier post and I'm hoping you can offer me a
bit more of follow up (original post was for Target Value question). I'm
hoping you can help me more regarding the solution due to I'm not sure
exactly how to set it up. I've also enclosed my original macro and more
information and maybe this will help to clear up some of the confusion I
might have caused.
____________________________________________________________________
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

 

[JLatham]

I think maybe this will help with the code - I presume that changes are being
detected in the range L18:L33 at the present time? And that as time goes on
this area could extend on down the sheet and need to be something like
L18:L101 ??

Try this code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim targetAddress As String
targetAddress = "L18:" & _
Range("L" & Rows.Count).End(xlUp).Address
If Not Application.Intersect(Target, Range(targetAddress)) Is Nothing Then
If Target.Value = 0 Then
Target.Offset(0, 0).Select
Selection.ClearContents
End If
End If

But I think that you're still looking at the Front Page sheet? If so, that
probably isn't going to help a lot. You need to be looking for changes on
the Data Page.

Would help to see any formulas in any of the cells involved, tell us which
cells on what sheet and what the formula in them is, or tell us if it is data
that is typed or pasted into them.
End Sub

 

[JLatham]

Crap!! Editor got in my way - I think the code got some of my comments
included in with it, and since we can't edit in this forum , I'll try
again:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim targetAddress As String
targetAddress = "L18:" & _
Range("L" & Rows.Count).End(xlUp).Address
If Not Application.Intersect(Target, Range(targetAddress)) Is Nothing Then
If Target.Value = 0 Then
Target.Offset(0, 0).Select
Selection.ClearContents
End If
End If
End Sub

 

[Jenny B.]

Hi J,

Sorry to be a Gadfly, but this unfortunately does the same as my existing
routine and I think it's due to my poor examples - let me try again.

This is the second of 2 separate workbooks. For the examples sake, I'll call
this WB2 and the other writing data to it WB1. WB1 uses an macro that not
only writes data to the "Front Page" of WB2, but copies data to "Data Page"
in the very same WB2 book. The WB1 macro that deals with the Front Page is
shown below in EXP 1.

EXP. 1 is called later in the body of EXP 2 that is copying data from WB1
to WB2 (see EXP 2 below). Since the data is writing to a group of 10
rotating, hidden sheets - that's where my dilemma comes into play. Cell "B5"
in WB2 - "Data Page" is now linked to "Front Page" in whatever cell was next
in line. Later, when I"m done with the "Data Page" and clear the contents, I
was hoping that by putting the Target Value macro in the Front Page it would
immediately remove the lines that have now have a zero - doesn't work that
way. Since I'm not actually entering a zero and it's the "Data Page" now
being gone that enters that value - the Target Value formula fails.

I know this is confusing without a pictorial, but I'd appreciate it very
much if you could further decipher my babble and let me know if you have any
further thoughts.

Appreciate anything you come up with and thanks again for your prompt reply
- Jenny B.


EXP. 1

Sub WritetoMainPage()

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

irow = ws.Cells(1, 12) _
.End(xlDown).Offset(1, 0).Row

ws.Cells(irow, 12).Formula _
= "=" & ActiveSheet.Range("h5").Address(external:=True)

Worksheets("sheet1").Select

End Sub

Exp 2

For i = 1 To 10
If Worksheets("T" & i).Range("A1") = "" Then
Worksheets("T" & i).Visible = True
Worksheets("T" & i).Select

(the rest of the body is a simple copy to)
WB2 next empty sheet macro and later calls EXP 1
to write to Front Page

 

[JLatham]

Jenny,
I'm looking over your last informational posting and trying to get my head
wrapped around what's going on. Be patient - I'm not ignoring you.

 

[JLatham]

Jenny,
It sounds to me that when you clear a Data Page, you need to go check Front
Page and see if there are zero values, or even be specific and test to see if
cells on it have formulas referring to the Data Page (which I presume is one
of your T# sheets?). When you find cell(s) on Front Page that you want to
remove the row from, you do it from within that routine. (If you're actually
deleting the rows, I find it easiest to do it from the bottom up in code).

Your Data Page(s) probably need a Worksheet_Change() event handler that
looks at a specific cell to see if it changed to "" and if it did, then go
deal with Front Page.

Your event trapping for Front Page isn't working because the changes to that
sheet are being done via the formula, and Excel doesn't recognize them as a
change event on that sheet. That's why both Mike H and myself said go back
and test for changes on the source sheets (the data sheets) that have some
effect on the results on Front Page, and when the data sheets have changed,
that is when you then just go test for conditions on Front Page that require
a row deletion. Heck, if the testing for condition to delete rows on Front
Page are too complex, you could simply put a button on them attached to a
macro that goes and tests entries on Front Page to see if they need to be
deleted. Remember that while you have one of the data sheets selected, you
can get its name via ActiveSheet.Name if you want to examine formulas on the
Front Page sheet. Just look for the sheet name in each linked cell's formula
- but watch out since if you just test for "T1", you may delete rows that are
linked to "T10". If you tested for sheet name with a ! added to it, that
will get over that hump: testText = Activesheet.Name & "!" and then go check
formulas to see if they contain that.

 

[Jenny B.]

Hi J,

Thanks once again for your patience.

Your idea will work just great and I appreciate you taking the time to
re-review this.

Take care and thanks again - Jenny B.