Many threads have been devoted to this--few
seemingly to any avail.

I'm surprised you say that, there must be dozens if not hundreds of threads
that suggest autofit column width, I suggested same in the other thread you
referred to so there's one at least! Thereafter it depends on the overall
objective.

As I also mentioned in the other thread, you can also use an "autosize"
textbox which I think has some advantages over autofit'ing column widths.
Have a go with the following -


Sub test()
Dim bDeleteTextBox As Boolean
Dim c As Long
Dim s As String
Dim sIndentExtraLines As String
Dim shp As Shape

Dim cel As Range

Rows(1).Clear
Rows(1).RowHeight = ActiveSheet.StandardHeight

s = "This bunch of words should wrap to cell width such that"
s = s & " second and subsequent lines are indented, or even"
s = s & " include (say) a dash or some other character"

sIndentExtraLines = " - "

For c = 1 To 10
Set cel = Cells(1, c)

Columns(c).ColumnWidth = c * 3 + 20

With cel.Font
.Size = c + 5
.Bold = c Mod 2
End With

bDeleteTextBox = CBool(c = 10) ' if don't need the textbox again

' if might need the tbx in future leave it invisible on the sheet
' and ensure bDeleteTextBox is passed = false

TextLFtoCellWd cel, s, sIndentExtraLines, shp, bDeleteTextBox

Next

Rows(1).EntireRow.AutoFit

End Sub


Function TextLFtoCellWd(cel As Range, _
ByVal sText, _
Optional sIndent As String, _
Optional shpTB As Shape, _
Optional bDelTB As Boolean)
Dim i As Long, c As Long
Dim s As String
Dim s1 As String, s2 As String, s3 As String
Dim w As Single
Dim v
Dim shp As Shape
Dim fnt As Font


If shpTB Is Nothing Then
getTB shpTB
End If
Set fnt = shpTB.TextFrame.Characters.Font

'cel.Clear ' to avoid potential error below with mixed fonts

With cel.Font
fnt.Name = .Name
fnt.Size = .Size
fnt.Bold = .Bold
fnt.Italic = .Italic
End With

' remove any carriage returns & line feeds
sText = Replace(sText, vbCr, " ")
sText = Replace(sText, vbLf, " ")

v = Split(sText, " ")

' s1 = "": s2 = "": s3 = ""

w = cel.Width - 5

For i = 0 To UBound(v)
s1 = s1 & v(i)
shpTB.TextFrame.Characters.Text = s1
If shpTB.Width w Then
s3 = s3 & s2 & vbLf
s1 = sIndent & v(i)
s2 = s1
Else
s2 = s1
End If
If i < UBound(v) Then s1 = s1 & " "
Next

s3 = s3 & s2

cel.WrapText = True
cel.Value = s3

If bDelTB Then
shpTB.Delete
End If

End Function

Function getTB(shp As Shape) As Boolean

On Error Resume Next
Set shp = ActiveSheet.Shapes("TextLen")
On Error GoTo 0

If shp Is Nothing Then
Set shp = ActiveSheet.Shapes.AddTextbox(1, 0, 0, 1, 20)
shp.Name = "TextLen"
End If

With shp.TextFrame
.AutoMargins = False
.AutoSize = True
.MarginLeft = 0
.MarginRight = 0
End With

shp.Visible = msoFalse ' uncomment for testing

End Function

'''''''''''''''

If not, then I need an intermediate UDF to return either:

Can't use this approach in a UDF as UDFs can only return values (can't do
the intermediary fit stuff). In any case, a UDF would not help if you need
to adjust if user re-sizes column width (format changes do not trigger
recalc / UDF).

It might be possible to develop the GetTextExtentPoint32 approach in a UDF
that I started in the other thread. I didn't pursue it at the time as I
assumed with no follow-up it was not of any interest.

we all know and love called Lotu--I mean Excel.

Amusing, and FWIW my preferred app a long time ago was Quatro Pro. However
one of the reasons Excel won over both was the infinitely superior VB/A,
particularly from XL95, vs the respective macro systems.

Regards,
Peter T

wrote in message
...
I should state what I really want first:

I've got a long sentence in a wrapped cell. (10 to 15 in Sing Sing.
Send money.)

The 2nd & succeeding lines of text must be indented. (I'll gladly
settle for leading spaces.) E.g.:


This is a long string which resides in a single
wrapped cell of that wonderful application which
we all know and love called Lotu--I mean Excel.


Has anyone coded this functionality?

***

If not, then I need an intermediate UDF to return either:

- The string formatted with, say, vbLF(s) demarcating Excel's splits
(based on the width of the cell where the string lives, natch).

- The string position of the first split. (Since I may have to
reiterate the function each time I indent, thereby altering string
length a tad, that's maybe all I need.)

- Cell width translated into even a *semblance* of a number with which
to take a substring. Many threads have been devoted to this--few
seemingly to any avail.

And yes, I've played around a fair amount, futilely, with
GetTextExtentPoint32--with unending thanks to Peter T. See Jan. 2008
thread, "How do you x-late GetTextExtentPoint32's units into the real
world?"

Also see May 2006 thread, "How to determine whether text in cell needs
to be wrapped?". A question that could not be more clearly stated--yet
went unanswered as posed.

So how do you hook into Excel's wrap method?

Thanks much.

***