Shapes TextBox AutoSize

[Warship]

I'm trying to Automatically fit the text to the size of the TextBox frame using:

    With obFinalNote.TextFrame2
        strTxt = .TextRange
        .DeleteText
        .WordWrap = msoTrue
        .AutoSize = msoAutoSizeTextToFitShape
        .TextRange = strTxt
    End With

It appears ".AutoSize = msoAutoSizeTextToFitShape" only considers the width (I think) as the text is downsized but yet the text still extends beyond the the bottom of the frame. And never gets set to less than 11 point. Even when there's not enough text to reach the bottom of the frame the text is still set to 11pt leaving the frame half empty. Need to have text big as possible.

Any suggestions on how to keep text in its entirety within the frame of the shape?


The quantity of text varies widely and the frame size cannot change. I've tried using Len() and dividing by a certain number and then based on that answer set the size with:

    With obFinalNote.TextFrame.Characters.Font
        .Name = "Arial"
        .FontStyle = "Normal"
        .Size = NoteFontSize
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With

But this technique doesn't seem reliable as the number to divide the Len() by seems to depend on how "wraps" have occured.

Suggestions??

 

[Jaafar Tribak]

Tey this:

    With obFinalNote.TextFrame2
        strTxt = .TextRange
        .DeleteText
       [COLOR=Red][B] .WarpFormat = msoWarpFormat1[/B][/COLOR]
        .WordWrap = msoTrue
        .AutoSize = msoAutoSizeTextToFitShape
        .TextRange = strTxt
    End With

 

[Warship]

That worked! THANK YOU! THANK YOU!

I seen WarpFormat in the Object Browser, couldn't get it to work and couldn't find much info on it.

There are 36 of these WarpFormats, where can I find documentation on them?

Again, many many thanks.

 

[Jaafar Tribak]

That worked! THANK YOU! THANK YOU!

I seen WarpFormat in the Object Browser, couldn't get it to work and couldn't find much info on it.

There are 36 of these WarpFormats, where can I find documentation on them?

Again, many many thanks.

I haven't searched for any info on that Property, I just tried it blindly and it worked

 

[Warship]

Not sure what I was doing wrong when I tried it originally, but something obviuosly.

BTW I also tried some of the others which produce a wide range of effects such as:
Type over a bubble
Around the corner of a wall - inside and outside corners
Coming at you - going away from you
Etc etc.

Neat effects though I have no idea when I'd ever use them, but then you never know what the next project may bring.

msoWarpFormat1 does condense the type a little but at least it's all in the box and it fills the space plus it will rarely be needed in this project.

Thanks again for your experimenation.

 

[supernovadxg]

Hello All. Funny enough this is the only message board I've been able to find with a solution to this issue (and I've search hi and low). I tried implementing the code and it works but with a few quirks. Firstly, the text in the shape only shrinks if I click inside the shape. The moment I click outside of it the text goes back to it's original size. In hindsight, that might be dependent on where I pasted the code (i.e. worksheet versus separate module)?? I'm really new to VBA as I'm sure you can tell.

Also, the moment that I delete the text in the shape, any new text resizes and is enormous. Any way to prevent this?

Lastly, I wondered what the reason was for the following line in the code:

For Each shp In ActiveSheet.Shapes
With shp.TextFrame2
strTxt = .TextRange
.DeleteText --------> why delete the text????

Your help with this is hugely appreciated!!!

D.