Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Follow up for Mike H or J Latham
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Follow up for Mike H or J Latham
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 "Jenny B." wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Follow up for Mike H or J Latham
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." wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Follow up for Mike H or J Latham
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" wrote: 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." wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Follow up for Mike H or J Latham
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. "Jenny B." wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Follow up for Mike H or J Latham
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." wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Follow up for Mike H or J Latham
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. "JLatham" wrote: 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." wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need you Mike H! | Excel Worksheet Functions | |||
Subtracting Dates - Mike | Excel Discussion (Misc queries) | |||
min IF - Bob Philips follow up | Excel Worksheet Functions | |||
Thanks to Mike and Niek Otten | Excel Discussion (Misc queries) | |||
Mike | Excel Discussion (Misc queries) |