Uploaded spreadsheet - need help

[robert morris]

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.

 

[Tyro]

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]

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.

 

[Sandy Mann]

Place this code in the Worksheet module of the sheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Columns("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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

I forgot to add that I would leave any setting of the page breaks until you
are finished and are about to print.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Tyro]

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]

..>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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Tyro]

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]

He can do that by manually inserting a row

The OP asked:

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[robert morris]

..>I tried your code. It always does the Exit Sub.

It will if you are not in Column D. The code works for me.


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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[robert morris]

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.

 

[Tyro]

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")) 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

 

[Tyro]

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")) 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]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[robert morris]

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.

 

[Tyro]

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

 

[robert morris]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk