Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Programatically adjusting a text box height according to the font.

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
rkanter
Asked:
rkanter
  • 10
  • 8
  • 7
1 Solution
 
sebastienmCommented:
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
 
rkanterAuthor Commented:
Unfortunately it is just text wrapping.
0
 
pauloaguiaCommented:
To adjust the height automatically you can convert it to a TextFrame:

objShape.ConvertToFrame


Hope this helps

Paulo
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rkanterAuthor Commented:
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
 
pauloaguiaCommented:
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
 
sebastienmCommented:
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
 
rkanterAuthor Commented:
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
 
pauloaguiaCommented:
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
 
rkanterAuthor Commented:
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
 
rkanterAuthor Commented:
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
 
sebastienmCommented:
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
 
rkanterAuthor Commented:
That's what I'm doing.  It dies on the line
iTempHeight = objNewShape.Height
I can't get the height value.
0
 
sebastienmCommented:
that's weird 'cause it works for me -- Win2K, XL2K
paulo, does it work for you?

Sebastien
0
 
pauloaguiaCommented:
weird... I can... but it returns -1 ?????
0
 
pauloaguiaCommented:
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
 
pauloaguiaCommented:
Something like

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


(Without the DoEvents it enters an infinite loop???)
0
 
sebastienmCommented:
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
 
pauloaguiaCommented:
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
 
rkanterAuthor Commented:
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
 
pauloaguiaCommented:
(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
 
pauloaguiaCommented:
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
 
sebastienmCommented:
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
 
rkanterAuthor Commented:
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
 
pauloaguiaCommented:
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
 
sebastienmCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now