Very Urgent question about wraping

[mp3nick]

Hi dudes
I know no one did it before or even need it but I need to wrap the two-line-text inside the one cell like that if it's two lines, align them to be almost at the maximum of same size together (Nearest Same size), but I used to work with LEN which each character and word is not the exact and accurate Width used with ARIAL font I mean not the exact width , I hope I could say what I mean, is there any function like this ?

TextWidth("Your Text Here",YourFontName,YourFontSize) = Width for text in Pixel size format

Thanks alot

 

[bryanbayfield]

Sorry mate, haven't got a Scooby what you're after there?

Are you saying you want to change the width of a column so that it's the same width as the rows of text within the cells?

 

[SkipVought]

Hi,
I am not sure if this will give you exactly what you want, but it may be close.

I put your string into a Drawing Object with autosize and no margins. Then remove the autosize and retiieve the .Width...

Function TextWidth(sText As String, sFontName As String, nFontSize As Single) As Single
'
' Macro1 Macro
' Macro recorded 3/12/2003 by Skip Metzger
'

'
    ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal, 0, 0, 0#, 0#).Select
    Selection.Characters.Text = sText
    With Selection.ShapeRange(1).TextFrame
        .AutoSize = msoTrue
        .MarginLeft = 0#
        .MarginRight = 0#
        .MarginTop = 0#
        .MarginBottom = 0#
    End With
    With Selection.Font
        .Name = sFontName
        .FontStyle = "Regular"
        .Size = nFontSize
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With Selection
        .AutoSize = False
        TextWidth = .Width
        .Delete
    End With
End Function

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SimonFinn]

Hi Guys

Are u using an API here? If so what functions are u declareing?

If not: what version of Office are you using, ive been trying to return the pixel width of formatted text for a while now. Im using Office 2000 and from what ive found out TextWidth is a printer object function and only Office XP now supports (limited) printer object fuctionality

If anyone can help me with this issue i would be very greatful

Thanks Si

 

[SkipVought]

Simon,
No API, just a klugy attempt to approximate a solution using text in a drawing object and then getting the associated width of the drawing object. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

I did a little bit more work on it and modified the return line of code...

TextWidth = .Width - 6
[\code]
In my testing using "i" and "W"...
[code]
Text    Width
i       2.25
ii      4.50
W       9.75
WW     19.50

So it looks like my function returns a true width in points.
Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SimonFinn]

What type of drawing object are u using (im in MS Access)

 

[SkipVought]

MS Office shpae

msoTextOrientationHorizontal

check the code.

I wrote it in Excel VBA Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SimonFinn]

Hi Guys

I dont think i have that object, is there an alternetive?

Cheers Si

 

[SkipVought]

Add a reference to MS Office in your VBE Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SimonFinn]

I have checked the reference and its there (MS Office 10.0 Object Library)

Its still not there? Or is that the wrong reference?

 

[RobBroekhuis]

Here's another approach that may be helpful:

Function TextWidth(s As String) As Double
Dim cell As Range
Set cell = ActiveSheet.UsedRange.Cells(1, 1).Offset(0, ActiveSheet.UsedRange.Columns.Count)
cell = s
cell.EntireColumn.AutoFit
TextWidth = cell.ColumnWidth - 0.28
cell.Clear
End Function

This gives you the width of your text in the default font style and size of your worksheet. You could add additional parameters to the function if you need to use different formatting options. I'm not quite sure if the 0.28 (the cell margins) is a universal constant - it works for me.
Rob

 

[SimonFinn]

Hi Rob

That one doesnt seem to work for me, Im using MSFlexGrid

 

[SkipVought]

Simon,
If you're in Access, then you may need to add a reference to Excel to use either Rob's or mine, 'cuz they both use worksheet objects. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RobBroekhuis]

Another strike for identifying the application you're working with in your problem statement
Rob