?
Solved

Programatically adjusting a text box height according to the font.

Posted on 2003-03-21
25
Medium Priority
?
300 Views
Last Modified: 2013-12-03
I am adding a text box using the following code:
           
    Set objShape = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, _
        sLeftPos, _
        sTopPos, sWidthPos, sHeightPos)
'sHeightPos is set to the font size * 2
.
.
.
   With objShape
.
.
.            With .TextFrame
.
.
.
                With .TextRange
.
.
.
                    .Text = strBannerText

This works fine as long as the text within the box is only one line. If it is two line the box is not tall enough.  Is there a way to either have to box automatically adjust the height or is there a way go get the number of line of text within a box?
Thank you,
-Ron
0
Comment
Question by:rkanter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 7
25 Comments
 
LVL 16

Expert Comment

by:sebastienm
ID: 8183063
Where does strBannerText come from? ie. do you mean there are carriage returns in the strBannerText or the text is just wrapping?

If there are carriage returns in the text, you can count the number of chr(13) in strBannerText.
(use the NumberOfRows function bellow)

'---------------------------------------------------
Sub test()
   Dim s As String
   s= "Hello" & vbnewline & "World"
   'display number of rows in s
   MsgBox s & vbnewline & "--> " & NumberOfRows(s) & " rows."
End Sub

Function NumberOfRows(s As String) As Long

   Dim s1 As String
   Dim howManyCarriage As Long
   Dim i As Long
   
   s1 = s
   NumberOfRows = 0
   i = InStr(s1, Chr(13))
   Do While i <> 0
      NumberOfRows = NumberOfRows + 1
      s1 = IIf(i = Len(s1), "", Right(s1, Len(s1) - i))
      i = InStr(s1, Chr(13))
   Loop
   NumberOfRows = NumberOfRows + 1
End Function
'-------------------------------------------

So when you do  .Text = strBannerText
you can extend the Height of objShape based on
   NumberOfRows(strBannerText)


Regards,
Sebastien
0
 

Author Comment

by:rkanter
ID: 8183095
Unfortunately it is just text wrapping.
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8184045
To adjust the height automatically you can convert it to a TextFrame:

objShape.ConvertToFrame


Hope this helps

Paulo
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:rkanter
ID: 8184181
Well.The box adjusted correctly. Unfortunately it doesn't allow me to position the box within a table anymore.  Also one ohter thing to be aware of is that the box and text I am using can span more than one cell.  I am basically creating a calendar and appointments that are more than 24 hours are enclosed in a box.
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8184296
Well I'm more of an Excel guy myself. It was just a shot. Didn't know you had other restrictions...
I couldn't find a way to define the text wrapping... at least not for the text inside the shape.

In Excel I have a similar problem. Merged ranges don't wrap automatically. So I use this formula:

   rngCell.Height = (Len(strMEssage) * 8 \ rngCell.Width + 1)

This assumes a font of medium width 8. Also the cells height in Excel is given in terms of characters, not points (like the width is). So I guess you could adjust it to

  objShape.Height = (Len(strBannerText) * 8 \ objShape.Width + 8)

I think you also need to take into account the inner margins or something... It looks about the right size if I remove the margins from the border to the text. But I guess you know more about that than I do (but I can look into it if you wish).

Paulo
0
 
LVL 16

Expert Comment

by:sebastienm
ID: 8184350
If it works with a textframe , why not:
1. Create the textframe with the text
2. Assigne variable to remember its Width and Height (since it autoajusts)
3. Delete the TextFrame
4. Assign these dimensions to the texbox.

and maybe make the textframe non-visible at creation so that the user doesn't see the textframe poping up then disappear.

Regards,
Sebastien
0
 

Author Comment

by:rkanter
ID: 8184429
Sebastien,
Good Idea if I could get it to work.  Once I convert it to a text frame it doesn't allow me to get the height.  I get an error that the object has been deleted.
-Ron
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8184459
That method returns a frame object and it's this object you need to get the height and width from.

Something like:

   frm = objShape.ConvertToFrame
   intHeight = frm.Height

Paulo
0
 

Author Comment

by:rkanter
ID: 8184499
Sebastien,
Good Idea if I could get it to work.  Once I convert it to a text frame it doesn't allow me to get the height.  I get an error that the object has been deleted.
-Ron
0
 

Author Comment

by:rkanter
ID: 8184515
Here is the code I tried but I still get the deleted error:

    Dim objShape As Word.Shape
    Dim objNewshape As Word.Frame
    Dim lNumBanners As Long
    Dim lCellCount As Long
dim iTempHeight  As Single
    Dim asTempCategories As String
    Dim i As Integer
   
           
    Set objShape = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, _
        sLeftPos, _
        sTopPos, sWidthPos, sHeightPos)
                   
    Set objNewShape = objShape.ConvertToFrame
iTempHeight = objNewShape.Height
0
 
LVL 16

Expert Comment

by:sebastienm
ID: 8184562
what about the following?

   Dim objShape As Word.Shape
   Dim objNewshape As Word.Frame
   Dim lNumBanners As Long
   Dim lCellCount As Long
   dim iTempHeight  As Single
   Dim asTempCategories As String
   Dim i As Integer
   
           
   Set objShape = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, _
       sLeftPos, sTopPos, sWidthPos, sHeightPos)
                   
   Set objNewShape = objShape.ConvertToFrame
'Now i guess objShape doesn't exist anymore since it was changed into a frame (deleted)
iTempHeight = objNewShape.Height

'Now recreate the Textbox with new height
Set objShape = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, _
       sLeftPos, sTopPos, sWidthPos, iTempHeight)
'delete Frame
objNewshape.Delete
0
 

Author Comment

by:rkanter
ID: 8184576
That's what I'm doing.  It dies on the line
iTempHeight = objNewShape.Height
I can't get the height value.
0
 
LVL 16

Expert Comment

by:sebastienm
ID: 8184589
that's weird 'cause it works for me -- Win2K, XL2K
paulo, does it work for you?

Sebastien
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8184590
weird... I can... but it returns -1 ?????
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8184617
I just found out a nice property:

--
If MyTBox.TextFrame.Overflowing = True Then
--

Maybe you can use this inside a cycle to keep increasing the height until text doesn't overflow anymore?
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8184626
Something like

Do While objShape.TextFrame.Overflowing = True
    DoEvents
    objShape.Height = objShape.Height + 10
Loop


(Without the DoEvents it enters an infinite loop???)
0
 
LVL 16

Expert Comment

by:sebastienm
ID: 8184639
Nice trick, Paulo.

Finally, one of the many questions i participated in today will be resolved ... it was not an easy day :-)   Thanks Paulo.

Sebastien
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8184647
I hope it is resolved. I'm one question away from top 15 and haven't received a single Good Answer mail all night...
0
 

Author Comment

by:rkanter
ID: 8184662
I hate to tell you this but it still gets caught in an endless loop.  What's strange is if I put a breakpoint in and step through the code it works fine.
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8184692
(why today??????)

I had the same response... infinite loop but ok when going step by step... that's why I added the DoEvents. It usually does the trick (and still does for me).

Sebastienm do you have any suggestion?
0
 
LVL 9

Accepted Solution

by:
pauloaguia earned 800 total points
ID: 8184729
By the way, I'm using Office 2k. What is your version?

Also, I managed to get it stuck when I forgot to initialize the width. Which makes sense, since in a "thin" box the text will never fit.

I suppose you should also add an height limit as a stop condition. Like

Do While objShape.TextFrame.Overflowing And objShape.Height < 500
   objShape.Height = objShape.Height + 10
Loop

(by the way now I don't get an endless loop without DoEvents...)
You can set this stop condition to the size of your page or whatever you like...
0
 
LVL 16

Expert Comment

by:sebastienm
ID: 8184749
Paulo, your code works for me (with or without the DoEvents), so it's hard to think about anything to solve this problem.
Maybe you should try to launch a method of the object to make it react, like Select method. But frankly i don't know... sorry.

Sebastien
0
 

Author Comment

by:rkanter
ID: 8186582
Since it will never be more than three lines I could put in a check.  Thank you both for your help.  I wish I could give points to both of you but since I can't I will give them to the person who came up with the overflow idea first.
Thank you,
-Ron
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8187367
You can give points to both of us if you want to. Just post a 0 point question in Community Support Topic Area with a link to this one asking for a moderator to split the points. He'll also tell what else you must do.

Paulo
0
 
LVL 16

Expert Comment

by:sebastienm
ID: 8187384
it's ok, paulo, you deserve the points: you're the one who found the Overflowing trick and the ConvertToFrame idea.
Thank you though.

Regards,
Sebastien
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question