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]

What kind of textbox is it? Drawing toolbar or Control Toolbox created? Also, is that sheet's zoom percentage set to anything other than 100%?

 

[RSimmons]

The sheet's zoom percentage is set at 100%, yes. I believe it is a drawing toolbar text box (been awhile since I started this so I am not completely sure), although when I go to format it I do get all the option tabs (size, alignment, protection, etc).

 

[RoryA]

What happens if you comment out this line in the NarrativePrintArea routine:

Worksheets("narrative").Shapes("text box 16").Select

 

[RSimmons]

Just tried it. Same thing I'm afraid - afterwards the box looks the same size but the properties say it is about 2" x 1".

Thanks -

Rebecca

 

[RoryA]

Is the PageSetup scaling when printing? (in case you can't tell, this isn't a problem I'm familiar with specifically, so I'm fishing a bit! )
Can you upload the file somewhere or email it so I can test it?

 

[RSimmons]

PageSetup scaling - I'm afraid I don't know exactly what you mean, but if you mean does the page scaling change during the print, no, I don't believe so. (The print area changes, of course, when the macro runs - but the page isn't scaled down or up. At least not so far as I know.)

Let me check in to whether I am allowed to send the form outside the company.... I would love to have another set of eyes look at it.

 

[RoryA]

What I mean is in the Page setup is it set to fit to a certain number of pages , or a certain % when it prints?

 

[RSimmons]

Nope, at least the Narrative page is not.

Thx -

Rebecca

 

[RSimmons]

OK, got the go-ahead from my boss to send the form, so I could email it to you if you would still be willing to give it a look. Where should I send it?

Thanks!

-R