Print dialog seems to resize a text box - ?

[RSimmons]

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

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 Sub

Here’s the workarounds I have tried so far:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><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.

 

[RoryA]

I will PM you my email address shortly.

 

[RoryA]

OK I see what you mean - the property values change even though the textbox itself does not change size on the sheet. Incidentally, is you simply print preview the sheet afterwards, the properties reset themselves properly. YOu can work around the issue in your code by altering it to something like this:

Sub NarrativePrintArea()
'
'Sees how big the text box on the Narrative tab is, and adjusts print area accordingly.
'
'Macro written 12/17/08 by Rebecca Simmons.
'
Dim Parea As String
With Worksheets("Narrative")
    .Unprotect Password:="sword"
    Parea = "$a$15:" & .Shapes("text box 16").BottomRightCell.Address
    .PageSetup.PrintArea = Parea
    .Protect Password:="sword", DrawingObjects:=False, contents:=True, Scenarios:=True
    .EnableSelection = xlUnlockedCells
End With
'
End Sub

if that helps?
I don't know the actual cause, but will see if I can find any information on it.

 

[RSimmons]

THANK YOU!! This has seriously given me so much heartburn over the past week or two.... I'll go mark this "solved" right now. While I would love to know why it happens, just knowing how to work around it is much farther than I was getting and is really all I need.

I really appreciate your help on this.

-Rebecca

 

[RoryA]

Glad to help.
Just so you know, you don't have to mark threads as Solved here. (nor do we beat people over the head for incorrect spelling or slightly vague post titles etc. unlike some other forums... )

 

[RSimmons]

Well, I still wanted to edit the original message to show that there was a solution way down in the thread... but apparently I'm too late to edit it. Oh well!