|
Uploaded spreadsheet - need help
I have uploaded a spread sheet with the following link; http://www.freefilehosting.net/download/3a4i8 Two questions; One - is there a way to auto insert additional rows within the "bordered" areas past the two rows I have for each payment? Some sub-contractors will only need two rows, others with larger contracts will need more. Two - When we insert additional rows, is there a way to auto change the page breaks without separating or breaking up a "bordered" area? Thanks in advance for any help, Bob M. |
Uploaded spreadsheet - need help
There is no "auto" way. You have to manually insert the number of rows you
want. As for page breaks, Excel does not recognize your bordered areas as meaning anything. You have so few pages that you can easily check the printing in print preview mode and manually insert the page breaks. I inserted new rows, checked print preview and inserted page breaks in less than a minute. tyro "robert morris" wrote in message ... I have uploaded a spread sheet with the following link; http://www.freefilehosting.net/download/3a4i8 Two questions; One - is there a way to auto insert additional rows within the "bordered" areas past the two rows I have for each payment? Some sub-contractors will only need two rows, others with larger contracts will need more. Two - When we insert additional rows, is there a way to auto change the page breaks without separating or breaking up a "bordered" area? Thanks in advance for any help, Bob M. |
Uploaded spreadsheet - need help
Tyro, Thanks for your reply. I could find no way to accomplish this and you have verified this. I shall spend no more time on it. One other thing, this is Phase One of Three and when almost complete I estimate the total sheets to be approximately 20-25. Thanks again, Bob M. "Tyro" wrote: There is no "auto" way. You have to manually insert the number of rows you want. As for page breaks, Excel does not recognize your bordered areas as meaning anything. You have so few pages that you can easily check the printing in print preview mode and manually insert the page breaks. I inserted new rows, checked print preview and inserted page breaks in less than a minute. tyro "robert morris" wrote in message ... I have uploaded a spread sheet with the following link; http://www.freefilehosting.net/download/3a4i8 Two questions; One - is there a way to auto insert additional rows within the "bordered" areas past the two rows I have for each payment? Some sub-contractors will only need two rows, others with larger contracts will need more. Two - When we insert additional rows, is there a way to auto change the page breaks without separating or breaking up a "bordered" area? Thanks in advance for any help, Bob M. |
Uploaded spreadsheet - need help
Place this code in the Worksheet module of the sheet.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Cells(Target.Row + 1, "D").Value = "Balance Remaining" Then Cells(Target.Row + 1, "D").EntireRow.Insert End If End Sub Every time that you enter something in the row immediately above the "Balance Remaining" row it will insert a new row. Make sure that the row above the "Balance Remaining" is formatted the way you want *before* you enter data. You may also like to keep the column headers visible by selecting Row 5 and selecting Windows Freeze Panes. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "robert morris" wrote in message ... I have uploaded a spread sheet with the following link; http://www.freefilehosting.net/download/3a4i8 Two questions; One - is there a way to auto insert additional rows within the "bordered" areas past the two rows I have for each payment? Some sub-contractors will only need two rows, others with larger contracts will need more. Two - When we insert additional rows, is there a way to auto change the page breaks without separating or breaking up a "bordered" area? Thanks in advance for any help, Bob M. |
Uploaded spreadsheet - need help
I tried your code. It always does the Exit Sub. Anyway, it looks like the
code is attempting to insert a new row if anything is entered in the row above "Balance Remaining". I don't think the OP wants that as that row may be the last one he wants data in and does not want a blank row below it. In other words, there is no way to tell if the OP has finished entering data for the particular category, only he can determine that. "Sandy Mann" wrote in message ... Place this code in the Worksheet module of the sheet. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Cells(Target.Row + 1, "D").Value = "Balance Remaining" Then Cells(Target.Row + 1, "D").EntireRow.Insert End If End Sub Every time that you enter something in the row immediately above the "Balance Remaining" row it will insert a new row. Make sure that the row above the "Balance Remaining" is formatted the way you want *before* you enter data. You may also like to keep the column headers visible by selecting Row 5 and selecting Windows Freeze Panes. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "robert morris" wrote in message ... I have uploaded a spread sheet with the following link; http://www.freefilehosting.net/download/3a4i8 Two questions; One - is there a way to auto insert additional rows within the "bordered" areas past the two rows I have for each payment? Some sub-contractors will only need two rows, others with larger contracts will need more. Two - When we insert additional rows, is there a way to auto change the page breaks without separating or breaking up a "bordered" area? Thanks in advance for any help, Bob M. |
Uploaded spreadsheet - need help
Tyro" wrote in message
.. ..I tried your code. It always does the Exit Sub. It will if you are not in Column D. The code works for me. above "Balance Remaining". I don't think the OP wants that as that row may be the last one he wants data in and does not want a blank row below it. If there isn't a blank row below it how can the OP possibly enter any more data if he wants to? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Tyro" wrote in message ... I tried your code. It always does the Exit Sub. Anyway, it looks like the code is attempting to insert a new row if anything is entered in the row above "Balance Remaining". I don't think the OP wants that as that row may be the last one he wants data in and does not want a blank row below it. In other words, there is no way to tell if the OP has finished entering data for the particular category, only he can determine that. "Sandy Mann" wrote in message ... Place this code in the Worksheet module of the sheet. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Cells(Target.Row + 1, "D").Value = "Balance Remaining" Then Cells(Target.Row + 1, "D").EntireRow.Insert End If End Sub Every time that you enter something in the row immediately above the "Balance Remaining" row it will insert a new row. Make sure that the row above the "Balance Remaining" is formatted the way you want *before* you enter data. You may also like to keep the column headers visible by selecting Row 5 and selecting Windows Freeze Panes. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "robert morris" wrote in message ... I have uploaded a spread sheet with the following link; http://www.freefilehosting.net/download/3a4i8 Two questions; One - is there a way to auto insert additional rows within the "bordered" areas past the two rows I have for each payment? Some sub-contractors will only need two rows, others with larger contracts will need more. Two - When we insert additional rows, is there a way to auto change the page breaks without separating or breaking up a "bordered" area? Thanks in advance for any help, Bob M. |
Uploaded spreadsheet - need help
If the OP is in the row above "Balance Remaining" and enters data, that may
be his last row of data. You cannot programatically determine if he needs another row. He can do that by manually inserting a row or setting up a hot key activated macro to do it for him. Your method will work fine if he always wants a blank row above "Balance Remaining" but his example did not show a blank row between the last row with data and "Balance Remaining". Tyro "Sandy Mann" wrote in message ... Tyro" wrote in message .. .I tried your code. It always does the Exit Sub. It will if you are not in Column D. The code works for me. above "Balance Remaining". I don't think the OP wants that as that row may be the last one he wants data in and does not want a blank row below it. If there isn't a blank row below it how can the OP possibly enter any more data if he wants to? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Tyro" wrote in message ... I tried your code. It always does the Exit Sub. Anyway, it looks like the code is attempting to insert a new row if anything is entered in the row above "Balance Remaining". I don't think the OP wants that as that row may be the last one he wants data in and does not want a blank row below it. In other words, there is no way to tell if the OP has finished entering data for the particular category, only he can determine that. "Sandy Mann" wrote in message ... Place this code in the Worksheet module of the sheet. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Cells(Target.Row + 1, "D").Value = "Balance Remaining" Then Cells(Target.Row + 1, "D").EntireRow.Insert End If End Sub Every time that you enter something in the row immediately above the "Balance Remaining" row it will insert a new row. Make sure that the row above the "Balance Remaining" is formatted the way you want *before* you enter data. You may also like to keep the column headers visible by selecting Row 5 and selecting Windows Freeze Panes. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "robert morris" wrote in message ... I have uploaded a spread sheet with the following link; http://www.freefilehosting.net/download/3a4i8 Two questions; One - is there a way to auto insert additional rows within the "bordered" areas past the two rows I have for each payment? Some sub-contractors will only need two rows, others with larger contracts will need more. Two - When we insert additional rows, is there a way to auto change the page breaks without separating or breaking up a "bordered" area? Thanks in advance for any help, Bob M. |
Uploaded spreadsheet - need help
"Tyro" wrote in message
... He can do that by manually inserting a row The OP asked: Two questions; One - is there a way to auto insert additional rows "Tyro" wrote in message ... blank row above "Balance Remaining" but his example did not show a blank row between the last row with data and "Balance Remaining". On the contrary, apart form Rows 14 & 97 every, what the OP called "bordered" areas, has at least one blank row above the "Running Balance" Only Bob can tell us what he wants - if you haven't put him off by telling him that *There is no "auto" way* -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
Uploaded spreadsheet - need help
"Sandy Mann" wrote: Tyro" wrote in message .. ..I tried your code. It always does the Exit Sub. It will if you are not in Column D. The code works for me. above "Balance Remaining". I don't think the OP wants that as that row may be the last one he wants data in and does not want a blank row below it. If there isn't a blank row below it how can the OP possibly enter any more data if he wants to? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Tyro" wrote in message ... I tried your code. It always does the Exit Sub. Anyway, it looks like the code is attempting to insert a new row if anything is entered in the row above "Balance Remaining". I don't think the OP wants that as that row may be the last one he wants data in and does not want a blank row below it. In other words, there is no way to tell if the OP has finished entering data for the particular category, only he can determine that. "Sandy Mann" wrote in message ... Place this code in the Worksheet module of the sheet. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Cells(Target.Row + 1, "D").Value = "Balance Remaining" Then Cells(Target.Row + 1, "D").EntireRow.Insert End If End Sub Every time that you enter something in the row immediately above the "Balance Remaining" row it will insert a new row. Make sure that the row above the "Balance Remaining" is formatted the way you want *before* you enter data. You may also like to keep the column headers visible by selecting Row 5 and selecting Windows Freeze Panes. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "robert morris" wrote in message ... I have uploaded a spread sheet with the following link; http://www.freefilehosting.net/download/3a4i8 Two questions; One - is there a way to auto insert additional rows within the "bordered" areas past the two rows I have for each payment? Some sub-contractors will only need two rows, others with larger contracts will need more. Two - When we insert additional rows, is there a way to auto change the page breaks without separating or breaking up a "bordered" area? Thanks in advance for any help, Bob M. |
Uploaded spreadsheet - need help
Sandy,
I must have done something wrong. I copied your code into VBA module but, when I enter text in the row above "Balance Remaining", Col D, then tab to enter deposit paid, Col e, it enters 89 additional rows. What did I miss? Thanks, Bob M. "Sandy Mann" wrote: "Tyro" wrote in message ... He can do that by manually inserting a row The OP asked: Two questions; One - is there a way to auto insert additional rows "Tyro" wrote in message ... blank row above "Balance Remaining" but his example did not show a blank row between the last row with data and "Balance Remaining". On the contrary, apart form Rows 14 & 97 every, what the OP called "bordered" areas, has at least one blank row above the "Running Balance" Only Bob can tell us what he wants - if you haven't put him off by telling him that *There is no "auto" way* -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
Uploaded spreadsheet - need help
The problem is VBA is detecting the insertion of the new row which causes a
change and triggers the VBA code again and goes into a loop. Change the code to look like this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Cells(Target.Row + 1, "D").Value = "Balance Remaining" Then Application.EnableEvents = False Cells(Target.Row + 1, "D").EntireRow.Insert Application.EnableEvents = True End If End Sub Tyro "robert morris" wrote in message ... Sandy, I must have done something wrong. I copied your code into VBA module but, when I enter text in the row above "Balance Remaining", Col D, then tab to enter deposit paid, Col e, it enters 89 additional rows. What did I miss? Thanks, Bob M. |
Uploaded spreadsheet - need help
For consistency's sake you might want to put a blank row before every row
that has the word "Balance" in column D. Also "Balance Remaining" that the code checks for is not consistent. I noticed a "Balance to Rulon - $32-153" in column D. If you want the code to insert a new row when column D begins with "Balance " you could again change the code to Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Left(Cells(Target.Row + 1, "D").Value, 8) = "Balance " Then Application.EnableEvents = False Cells(Target.Row + 1, "D").EntireRow.Insert Application.EnableEvents = True End If End Sub Tyro "robert morris" wrote in message ... Sandy, I must have done something wrong. I copied your code into VBA module but, when I enter text in the row above "Balance Remaining", Col D, then tab to enter deposit paid, Col e, it enters 89 additional rows. What did I miss? Thanks, Bob M. "Sandy Mann" wrote: "Tyro" wrote in message ... He can do that by manually inserting a row The OP asked: Two questions; One - is there a way to auto insert additional rows "Tyro" wrote in message ... blank row above "Balance Remaining" but his example did not show a blank row between the last row with data and "Balance Remaining". On the contrary, apart form Rows 14 & 97 every, what the OP called "bordered" areas, has at least one blank row above the "Running Balance" Only Bob can tell us what he wants - if you haven't put him off by telling him that *There is no "auto" way* -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
Uploaded spreadsheet - need help
Tyro,
Beauuuutifull !! That does the job just as I wanted it. As to the "balance" paid to Rulon, should have been Invoice, Deposit, "Allocated to"; the word "balance" was just a typo. As to the dollar amounts, all that really counts is the "contract amount in Col E, pymts in Col F and Balance in Col G. I will continue to work with it and your second VBA. You people are so helpful and as we say in the deep south, "how you know all that stuff?" Last word cleaned up for publication on this format. Now, if we could only "auto" page break!! Again many thanks, Bob M. "Tyro" wrote: For consistency's sake you might want to put a blank row before every row that has the word "Balance" in column D. Also "Balance Remaining" that the code checks for is not consistent. I noticed a "Balance to Rulon - $32-153" in column D. If you want the code to insert a new row when column D begins with "Balance " you could again change the code to Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Left(Cells(Target.Row + 1, "D").Value, 8) = "Balance " Then Application.EnableEvents = False Cells(Target.Row + 1, "D").EntireRow.Insert Application.EnableEvents = True End If End Sub Tyro "robert morris" wrote in message ... Sandy, I must have done something wrong. I copied your code into VBA module but, when I enter text in the row above "Balance Remaining", Col D, then tab to enter deposit paid, Col e, it enters 89 additional rows. What did I miss? Thanks, Bob M. "Sandy Mann" wrote: "Tyro" wrote in message ... He can do that by manually inserting a row The OP asked: Two questions; One - is there a way to auto insert additional rows "Tyro" wrote in message ... blank row above "Balance Remaining" but his example did not show a blank row between the last row with data and "Balance Remaining". On the contrary, apart form Rows 14 & 97 every, what the OP called "bordered" areas, has at least one blank row above the "Running Balance" Only Bob can tell us what he wants - if you haven't put him off by telling him that *There is no "auto" way* -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
Uploaded spreadsheet - need help
First of all my apologies for not including the lines to turn off and on
Events but the code only fires once - I tried printing to the immediate window in the *If* statement and it only prints once - I'm using XL97, perhapd it's a version thing. To reset the page breaks at every entry would slow things down and that is why I suggested setting them only prior to printing. Referencing PrageBreaks seems to be very complicated as this thread and example from Tom Ogilvy shows: http://tinyurl.com/2vtxs9 You may be better posting your request in the programming group -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "robert morris" wrote in message ... Tyro, Beauuuutifull !! That does the job just as I wanted it. As to the "balance" paid to Rulon, should have been Invoice, Deposit, "Allocated to"; the word "balance" was just a typo. As to the dollar amounts, all that really counts is the "contract amount in Col E, pymts in Col F and Balance in Col G. I will continue to work with it and your second VBA. You people are so helpful and as we say in the deep south, "how you know all that stuff?" Last word cleaned up for publication on this format. Now, if we could only "auto" page break!! Again many thanks, Bob M. "Tyro" wrote: For consistency's sake you might want to put a blank row before every row that has the word "Balance" in column D. Also "Balance Remaining" that the code checks for is not consistent. I noticed a "Balance to Rulon - $32-153" in column D. If you want the code to insert a new row when column D begins with "Balance " you could again change the code to Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Left(Cells(Target.Row + 1, "D").Value, 8) = "Balance " Then Application.EnableEvents = False Cells(Target.Row + 1, "D").EntireRow.Insert Application.EnableEvents = True End If End Sub Tyro "robert morris" wrote in message ... Sandy, I must have done something wrong. I copied your code into VBA module but, when I enter text in the row above "Balance Remaining", Col D, then tab to enter deposit paid, Col e, it enters 89 additional rows. What did I miss? Thanks, Bob M. "Sandy Mann" wrote: "Tyro" wrote in message ... He can do that by manually inserting a row The OP asked: Two questions; One - is there a way to auto insert additional rows "Tyro" wrote in message ... blank row above "Balance Remaining" but his example did not show a blank row between the last row with data and "Balance Remaining". On the contrary, apart form Rows 14 & 97 every, what the OP called "bordered" areas, has at least one blank row above the "Running Balance" Only Bob can tell us what he wants - if you haven't put him off by telling him that *There is no "auto" way* -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
Uploaded spreadsheet - need help
Sandy,
Thanks so much for your input. People like you and this group helping people who in over their heads makes for a wonderful world. Tyro's fix made the code work perfectly. I also went to the link you referenced and looks like it could be more complicated than this small job requires. We currently set page breaks just before printing so we have not lost anything. I'm running Office 2007 Thanks again, Bob M. "Sandy Mann" wrote: First of all my apologies for not including the lines to turn off and on Events but the code only fires once - I tried printing to the immediate window in the *If* statement and it only prints once - I'm using XL97, perhapd it's a version thing. To reset the page breaks at every entry would slow things down and that is why I suggested setting them only prior to printing. Referencing PrageBreaks seems to be very complicated as this thread and example from Tom Ogilvy shows: http://tinyurl.com/2vtxs9 You may be better posting your request in the programming group -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "robert morris" wrote in message ... Tyro, Beauuuutifull !! That does the job just as I wanted it. As to the "balance" paid to Rulon, should have been Invoice, Deposit, "Allocated to"; the word "balance" was just a typo. As to the dollar amounts, all that really counts is the "contract amount in Col E, pymts in Col F and Balance in Col G. I will continue to work with it and your second VBA. You people are so helpful and as we say in the deep south, "how you know all that stuff?" Last word cleaned up for publication on this format. Now, if we could only "auto" page break!! Again many thanks, Bob M. "Tyro" wrote: For consistency's sake you might want to put a blank row before every row that has the word "Balance" in column D. Also "Balance Remaining" that the code checks for is not consistent. I noticed a "Balance to Rulon - $32-153" in column D. If you want the code to insert a new row when column D begins with "Balance " you could again change the code to Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Left(Cells(Target.Row + 1, "D").Value, 8) = "Balance " Then Application.EnableEvents = False Cells(Target.Row + 1, "D").EntireRow.Insert Application.EnableEvents = True End If End Sub Tyro "robert morris" wrote in message ... Sandy, I must have done something wrong. I copied your code into VBA module but, when I enter text in the row above "Balance Remaining", Col D, then tab to enter deposit paid, Col e, it enters 89 additional rows. What did I miss? Thanks, Bob M. "Sandy Mann" wrote: "Tyro" wrote in message ... He can do that by manually inserting a row The OP asked: Two questions; One - is there a way to auto insert additional rows "Tyro" wrote in message ... blank row above "Balance Remaining" but his example did not show a blank row between the last row with data and "Balance Remaining". On the contrary, apart form Rows 14 & 97 every, what the OP called "bordered" areas, has at least one blank row above the "Running Balance" Only Bob can tell us what he wants - if you haven't put him off by telling him that *There is no "auto" way* -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
Uploaded spreadsheet - need help
Sandy:
There is still one more little problem with the code. If the row 2 rows above the row containing the word "Balance" in column D is deleted, the row above the row with the word "Balance" becomes the active row and the code is activated because of the deletion. The code sees the word "Balance" in the row below and inserts a row. Tyro "Sandy Mann" wrote in message ... First of all my apologies for not including the lines to turn off and on Events but the code only fires once - I tried printing to the immediate window in the *If* statement and it only prints once - I'm using XL97, perhapd it's a version thing. To reset the page breaks at every entry would slow things down and that is why I suggested setting them only prior to printing. Referencing PrageBreaks seems to be very complicated as this thread and example from Tom Ogilvy shows: http://tinyurl.com/2vtxs9 You may be better posting your request in the programming group -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "robert morris" wrote in message ... Tyro, Beauuuutifull !! That does the job just as I wanted it. As to the "balance" paid to Rulon, should have been Invoice, Deposit, "Allocated to"; the word "balance" was just a typo. As to the dollar amounts, all that really counts is the "contract amount in Col E, pymts in Col F and Balance in Col G. I will continue to work with it and your second VBA. You people are so helpful and as we say in the deep south, "how you know all that stuff?" Last word cleaned up for publication on this format. Now, if we could only "auto" page break!! Again many thanks, Bob M. "Tyro" wrote: For consistency's sake you might want to put a blank row before every row that has the word "Balance" in column D. Also "Balance Remaining" that the code checks for is not consistent. I noticed a "Balance to Rulon - $32-153" in column D. If you want the code to insert a new row when column D begins with "Balance " you could again change the code to Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Left(Cells(Target.Row + 1, "D").Value, 8) = "Balance " Then Application.EnableEvents = False Cells(Target.Row + 1, "D").EntireRow.Insert Application.EnableEvents = True End If End Sub Tyro "robert morris" wrote in message ... Sandy, I must have done something wrong. I copied your code into VBA module but, when I enter text in the row above "Balance Remaining", Col D, then tab to enter deposit paid, Col e, it enters 89 additional rows. What did I miss? Thanks, Bob M. "Sandy Mann" wrote: "Tyro" wrote in message ... He can do that by manually inserting a row The OP asked: Two questions; One - is there a way to auto insert additional rows "Tyro" wrote in message ... blank row above "Balance Remaining" but his example did not show a blank row between the last row with data and "Balance Remaining". On the contrary, apart form Rows 14 & 97 every, what the OP called "bordered" areas, has at least one blank row above the "Running Balance" Only Bob can tell us what he wants - if you haven't put him off by telling him that *There is no "auto" way* -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
Uploaded spreadsheet - need help
Tyro,
I noticed that also however, it is not a problem at all. In the beginning I had two rows on all the different "bordered" areas. You both are to be commended on your interest in helping others. Bob M. "Tyro" wrote: Sandy: There is still one more little problem with the code. If the row 2 rows above the row containing the word "Balance" in column D is deleted, the row above the row with the word "Balance" becomes the active row and the code is activated because of the deletion. The code sees the word "Balance" in the row below and inserts a row. Tyro "Sandy Mann" wrote in message ... First of all my apologies for not including the lines to turn off and on Events but the code only fires once - I tried printing to the immediate window in the *If* statement and it only prints once - I'm using XL97, perhapd it's a version thing. To reset the page breaks at every entry would slow things down and that is why I suggested setting them only prior to printing. Referencing PrageBreaks seems to be very complicated as this thread and example from Tom Ogilvy shows: http://tinyurl.com/2vtxs9 You may be better posting your request in the programming group -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "robert morris" wrote in message ... Tyro, Beauuuutifull !! That does the job just as I wanted it. As to the "balance" paid to Rulon, should have been Invoice, Deposit, "Allocated to"; the word "balance" was just a typo. As to the dollar amounts, all that really counts is the "contract amount in Col E, pymts in Col F and Balance in Col G. I will continue to work with it and your second VBA. You people are so helpful and as we say in the deep south, "how you know all that stuff?" Last word cleaned up for publication on this format. Now, if we could only "auto" page break!! Again many thanks, Bob M. "Tyro" wrote: For consistency's sake you might want to put a blank row before every row that has the word "Balance" in column D. Also "Balance Remaining" that the code checks for is not consistent. I noticed a "Balance to Rulon - $32-153" in column D. If you want the code to insert a new row when column D begins with "Balance " you could again change the code to Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Left(Cells(Target.Row + 1, "D").Value, 8) = "Balance " Then Application.EnableEvents = False Cells(Target.Row + 1, "D").EntireRow.Insert Application.EnableEvents = True End If End Sub Tyro "robert morris" wrote in message ... Sandy, I must have done something wrong. I copied your code into VBA module but, when I enter text in the row above "Balance Remaining", Col D, then tab to enter deposit paid, Col e, it enters 89 additional rows. What did I miss? Thanks, Bob M. "Sandy Mann" wrote: "Tyro" wrote in message ... He can do that by manually inserting a row The OP asked: Two questions; One - is there a way to auto insert additional rows "Tyro" wrote in message ... blank row above "Balance Remaining" but his example did not show a blank row between the last row with data and "Balance Remaining". On the contrary, apart form Rows 14 & 97 every, what the OP called "bordered" areas, has at least one blank row above the "Running Balance" Only Bob can tell us what he wants - if you haven't put him off by telling him that *There is no "auto" way* -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
Uploaded spreadsheet - need help
Again it is a version thing. This thread says it is from XL2000 onwards:
http://tinyurl.com/2cebvt and this thread suggests that that there is a difference again in XL2003 http://tinyurl.com/2eexem -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Tyro" wrote in message . net... Sandy: There is still one more little problem with the code. If the row 2 rows above the row containing the word "Balance" in column D is deleted, the row above the row with the word "Balance" becomes the active row and the code is activated because of the deletion. The code sees the word "Balance" in the row below and inserts a row. Tyro "Sandy Mann" wrote in message ... First of all my apologies for not including the lines to turn off and on Events but the code only fires once - I tried printing to the immediate window in the *If* statement and it only prints once - I'm using XL97, perhapd it's a version thing. To reset the page breaks at every entry would slow things down and that is why I suggested setting them only prior to printing. Referencing PrageBreaks seems to be very complicated as this thread and example from Tom Ogilvy shows: http://tinyurl.com/2vtxs9 You may be better posting your request in the programming group -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "robert morris" wrote in message ... Tyro, Beauuuutifull !! That does the job just as I wanted it. As to the "balance" paid to Rulon, should have been Invoice, Deposit, "Allocated to"; the word "balance" was just a typo. As to the dollar amounts, all that really counts is the "contract amount in Col E, pymts in Col F and Balance in Col G. I will continue to work with it and your second VBA. You people are so helpful and as we say in the deep south, "how you know all that stuff?" Last word cleaned up for publication on this format. Now, if we could only "auto" page break!! Again many thanks, Bob M. "Tyro" wrote: For consistency's sake you might want to put a blank row before every row that has the word "Balance" in column D. Also "Balance Remaining" that the code checks for is not consistent. I noticed a "Balance to Rulon - $32-153" in column D. If you want the code to insert a new row when column D begins with "Balance " you could again change the code to Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub If Left(Cells(Target.Row + 1, "D").Value, 8) = "Balance " Then Application.EnableEvents = False Cells(Target.Row + 1, "D").EntireRow.Insert Application.EnableEvents = True End If End Sub Tyro "robert morris" wrote in message ... Sandy, I must have done something wrong. I copied your code into VBA module but, when I enter text in the row above "Balance Remaining", Col D, then tab to enter deposit paid, Col e, it enters 89 additional rows. What did I miss? Thanks, Bob M. "Sandy Mann" wrote: "Tyro" wrote in message ... He can do that by manually inserting a row The OP asked: Two questions; One - is there a way to auto insert additional rows "Tyro" wrote in message ... blank row above "Balance Remaining" but his example did not show a blank row between the last row with data and "Balance Remaining". On the contrary, apart form Rows 14 & 97 every, what the OP called "bordered" areas, has at least one blank row above the "Running Balance" Only Bob can tell us what he wants - if you haven't put him off by telling him that *There is no "auto" way* -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
| All times are GMT +1. The time now is 04:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com