I have a (semi-inherited) macro that prints different pages of a workbook, based on whether and what their entries are. One of the pages 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).
FWIW, I'm using Excel 2003.
Here's the code in question (the problem is on the "narrative" page):
Here’s the workarounds I have tried so far:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
First, wrote in code just before the “problem line” to lock the text box, and to completely protect the Narrative sheet (as opposed to the usual protections on it, which leave the text box unlocked) – then a line just after the problem line to reverse that. Unfortunately, this did no good; exact same result.<o></o>
<o></o>
Second, set two variables (BoxHeight, BoxWidth) and defined them as the height and the width of the box, respectively. Set that just before the problem line. Then just afterwards, I set the height and width of the box back to equal those values. Oddly enough, when I did that, the properties window now said the sizes were right, but the box appeared to be (and behaved as if it was) huge. In a way, the opposite problem.<o></o>
I am completely stumped. Can anyone point me at what I am missing? Thank you very much!!
This is a repost of a question I posted elsewhere (http://www.excelforum.com/editpost.php?do=editpost&p=2041658) last week; unfortunately, did not find a solution there and a friend referred me here. If I do get an answer there I will mention it here and mark this question solved, and vice versa.
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).
FWIW, I'm using Excel 2003.
Here's the code in question (the problem is on the "narrative" page):
Rich (BB code):
Sub CLCMprintall()
' prints all pages (with data) on the CLCM 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 SubHere’s the workarounds I have tried so far:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>First, wrote in code just before the “problem line” to lock the text box, and to completely protect the Narrative sheet (as opposed to the usual protections on it, which leave the text box unlocked) – then a line just after the problem line to reverse that. Unfortunately, this did no good; exact same result.<o
></o><o
></o>Second, set two variables (BoxHeight, BoxWidth) and defined them as the height and the width of the box, respectively. Set that just before the problem line. Then just afterwards, I set the height and width of the box back to equal those values. Oddly enough, when I did that, the properties window now said the sizes were right, but the box appeared to be (and behaved as if it was) huge. In a way, the opposite problem.<o
></o>I am completely stumped. Can anyone point me at what I am missing? Thank you very much!!
This is a repost of a question I posted elsewhere (http://www.excelforum.com/editpost.php?do=editpost&p=2041658) last week; unfortunately, did not find a solution there and a friend referred me here. If I do get an answer there I will mention it here and mark this question solved, and vice versa.
)
