Help me, LJ-land! You're my only hope!
So, I’m hoping one of you lovely, smart, helpful people has a better grasp of Excel and/or VBA programming than I do. This is driving me absolutely up a tree. I am more frustrated by this than I have been about anything in Excel in a long time, just because I can’t figure out how to approach the problem, much less solve it.
This is all part of a huge interactive form that folks have to fill out for loans. It contains a macro (among many other macros) that prints different pages of a workbook, based on whether and what their entries are. One of the pages (“Narrative”) contains a text box, where users will enter different lengths of text. When the macro runs, it prints right the first time but also "resizes" the text box so that future print attempts only print part of it.
I put "resize" in quotes because the text box does not appear to change shape. However, when I look at its properties it suddenly says it is much smaller than it was before (and still appears to be). That’s a problem, because the macro that sets the print area for the Narrative page uses the size of the text box to determine how much prints - so if the Narrative is more than 1 page long (which is normal) the remaining pages won’t print because the program thinks the box is tiny. It prints right the first time (since the macro getting the Narrative’s print area runs before the problem occurs), then afterwards screws up since the text box thinks it has been resized. Closing and reopening the file seems to fix it, but that’s not the best solution.
I can't figure out why on earth this is happening! Any thoughts?
Here's the code in question (like I said, the problem is on the "narrative" page):
Sub CLCMprintall()
prints all pages (with data) on the spreadsheet
Dim x, a, b, c, d, e, f, g, h, i As String
Dim y, z, q As Integer
ActiveSheet.Unprotect Password:="***"
notes active cells on each sheet to return cursor there afterwards.
x = ActiveSheet.Name
Sheets("page 1").Select
a = ActiveCell.Address(True, True)
Sheets("narrative").Select
d = ActiveCell.Address(True, True)
(repeat for other worksheets - also sets values for Loan2Amt, Q & Z,
which govern whether other unrelated sheets need to be printed.)
tests whether there is text in Overflow sheet and sets print area if there is
If q <> 0 Then
Call OverflowPrintArea
End If
tests whether there is a Loan 2 (by testing Loan 2 amt) and
adds Loan 2 collatersl to print area if there is
If Loan2amt > 0 Then
Call PrintColl2
End If
Calls macro that sets the Narrative's print area based on size of text box
Call NarrativePrintArea
tests whether there is anything on the notes page, addendum page,
or overflow, and includes them in the print area if there is
If z <> 0 And q <> 0 And y = 0 Then _
Sheets(Array("Page 1", "Collateral", "Page 1 Addendum", "Financial Info", "Narrative", _
"Covenants & Checklists", "Policy Reference Page", "GDSC", "Overflow")).Select _
Else
(lots of other options on the different permutations,
showing what to print in each case)
Application.Dialogs(xlDialogPrint).Show
HERE is where the problem appears - when I step thru it and
watch in the Immediate window, the text box size is fine before
the dialog command but artificially shrunken afterwards.
After the sheets are ungrouped, the spreadsheet defaults to
selecting the same cell on each spreadsheet, even though
it may be locked. This part of the macro selects the
correct starting cell on each sheet.
Sheets("page 1").Select
Range(a).Select
(etc etc for the rest of the sheets)
Sheets(x).Select
ActiveSheet.Protect Password:="***", DrawingObjects:=True,
contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
sets hidden areas on Overflow (that were hidden just for print area) back to un-hidden
If q <> 0 Then
Call OverflowRelock
End If
Sheets("page 1").Activate
ActiveSheet.Range("$c$7").Select
End Sub
Sub NarrativePrintArea()
Sees how big the text box on the Narrative tab is, and adjusts print area accordingly.
Dim Outside, BHeight, BRows, Parea As String
Worksheets("Narrative").Unprotect Password:="***"
BHeight = Worksheets("narrative").Shapes("text box 16").Height
BRows = BHeight / 16
Application.Goto reference:="picspace"
ActiveCell.Offset(BRows, 0).Range("a1").Select
Outside = ActiveCell.Address
Parea = "$a$15:" & Outside
Worksheets("narrative").PageSetup.PrintArea = Parea
Worksheets("narrative").Shapes("text box 16").Select
Worksheets("narrative").Protect Password:="***", DrawingObjects:=False, contents:=True, Scenarios:=True
Worksheets("narrative").EnableSelection = xlUnlockedCells
End Sub()
After playing with it for a while yesterday afternoon, I tried a couple workarounds which both failed miserably. First tried locking the text box (and then protecting the page, specifically so it also protected the object) right before the line of code that caused the problem - then unprotecting afterwards. Still resized it.
Also tried setting variables to the height & width of the text box just before the problem code line, then setting the height & width of the box back equal to those variables afterwards. Interestingly (but unhelpfully!), the values in the format menu of the box were now the same - but the box appeared huge.
It is making me crazy. I feel like I'm missing something very simple (units?) but no earthly clue what. Tried the usual suspects (tech support by Google, tech support by Megan...) with absolutely no luck.
Does anyone have any suggestions? At all??
Oh, and apologies in advance, if anyone is a real VBA-buff. I am sure this is inelegant. It's jury-rigged together; the base is stuff Megan wrote (and she has had just a bit of VBA training) scattered through with lots of stuff I wrote (and I have had absolutely none). It works, which is all I want of it.
Except when it doesn't work. Like now.
This is all part of a huge interactive form that folks have to fill out for loans. It contains a macro (among many other macros) that prints different pages of a workbook, based on whether and what their entries are. One of the pages (“Narrative”) contains a text box, where users will enter different lengths of text. When the macro runs, it prints right the first time but also "resizes" the text box so that future print attempts only print part of it.
I put "resize" in quotes because the text box does not appear to change shape. However, when I look at its properties it suddenly says it is much smaller than it was before (and still appears to be). That’s a problem, because the macro that sets the print area for the Narrative page uses the size of the text box to determine how much prints - so if the Narrative is more than 1 page long (which is normal) the remaining pages won’t print because the program thinks the box is tiny. It prints right the first time (since the macro getting the Narrative’s print area runs before the problem occurs), then afterwards screws up since the text box thinks it has been resized. Closing and reopening the file seems to fix it, but that’s not the best solution.
I can't figure out why on earth this is happening! Any thoughts?
Here's the code in question (like I said, the problem is on the "narrative" page):
Sub CLCMprintall()
prints all pages (with data) on the spreadsheet
Dim x, a, b, c, d, e, f, g, h, i As String
Dim y, z, q As Integer
ActiveSheet.Unprotect Password:="***"
notes active cells on each sheet to return cursor there afterwards.
x = ActiveSheet.Name
Sheets("page 1").Select
a = ActiveCell.Address(True, True)
Sheets("narrative").Select
d = ActiveCell.Address(True, True)
(repeat for other worksheets - also sets values for Loan2Amt, Q & Z,
which govern whether other unrelated sheets need to be printed.)
tests whether there is text in Overflow sheet and sets print area if there is
If q <> 0 Then
Call OverflowPrintArea
End If
tests whether there is a Loan 2 (by testing Loan 2 amt) and
adds Loan 2 collatersl to print area if there is
If Loan2amt > 0 Then
Call PrintColl2
End If
Calls macro that sets the Narrative's print area based on size of text box
Call NarrativePrintArea
tests whether there is anything on the notes page, addendum page,
or overflow, and includes them in the print area if there is
If z <> 0 And q <> 0 And y = 0 Then _
Sheets(Array("Page 1", "Collateral", "Page 1 Addendum", "Financial Info", "Narrative", _
"Covenants & Checklists", "Policy Reference Page", "GDSC", "Overflow")).Select _
Else
(lots of other options on the different permutations,
showing what to print in each case)
Application.Dialogs(xlDialogPrint).Show
HERE is where the problem appears - when I step thru it and
watch in the Immediate window, the text box size is fine before
the dialog command but artificially shrunken afterwards.
After the sheets are ungrouped, the spreadsheet defaults to
selecting the same cell on each spreadsheet, even though
it may be locked. This part of the macro selects the
correct starting cell on each sheet.
Sheets("page 1").Select
Range(a).Select
(etc etc for the rest of the sheets)
Sheets(x).Select
ActiveSheet.Protect Password:="***", DrawingObjects:=True,
contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
sets hidden areas on Overflow (that were hidden just for print area) back to un-hidden
If q <> 0 Then
Call OverflowRelock
End If
Sheets("page 1").Activate
ActiveSheet.Range("$c$7").Select
End Sub
Sub NarrativePrintArea()
Sees how big the text box on the Narrative tab is, and adjusts print area accordingly.
Dim Outside, BHeight, BRows, Parea As String
Worksheets("Narrative").Unprotect Password:="***"
BHeight = Worksheets("narrative").Shapes("text box 16").Height
BRows = BHeight / 16
Application.Goto reference:="picspace"
ActiveCell.Offset(BRows, 0).Range("a1").Select
Outside = ActiveCell.Address
Parea = "$a$15:" & Outside
Worksheets("narrative").PageSetup.PrintArea = Parea
Worksheets("narrative").Shapes("text box 16").Select
Worksheets("narrative").Protect Password:="***", DrawingObjects:=False, contents:=True, Scenarios:=True
Worksheets("narrative").EnableSelection = xlUnlockedCells
End Sub()
After playing with it for a while yesterday afternoon, I tried a couple workarounds which both failed miserably. First tried locking the text box (and then protecting the page, specifically so it also protected the object) right before the line of code that caused the problem - then unprotecting afterwards. Still resized it.
Also tried setting variables to the height & width of the text box just before the problem code line, then setting the height & width of the box back equal to those variables afterwards. Interestingly (but unhelpfully!), the values in the format menu of the box were now the same - but the box appeared huge.
It is making me crazy. I feel like I'm missing something very simple (units?) but no earthly clue what. Tried the usual suspects (tech support by Google, tech support by Megan...) with absolutely no luck.
Does anyone have any suggestions? At all??
Oh, and apologies in advance, if anyone is a real VBA-buff. I am sure this is inelegant. It's jury-rigged together; the base is stuff Megan wrote (and she has had just a bit of VBA training) scattered through with lots of stuff I wrote (and I have had absolutely none). It works, which is all I want of it.
Except when it doesn't work. Like now.