This won't give you the exact width, but it will give the relative width, so
maybe it could help you out.

Option Explicit
Private Type POINTAPI
X As Long
Y As Long
End Type
Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type
Private Declare Function GetWindowRect _
Lib "user32" (ByVal hwnd As Long, _
lpRect As RECT) As Long
Private Declare Function GetTextExtentPoint32 _
Lib "gdi32" _
Alias "GetTextExtentPoint32A" _
(ByVal hdc As Long, _
ByVal lpsz As String, _
ByVal cbString As Long, _
lpSize As POINTAPI) As Long
Private Declare Function GetWindowDC _
Lib "user32" (ByVal hwnd As Long) As Long

Function GetTextSize(strText As String) As Long()

Dim lHwnd As Long
Dim WR As RECT
Dim lDC As Long
Dim TextSize As POINTAPI
Dim Result(1 To 2) As Long

'get the Excel application hWnd
'This may need to be a different hWnd
lHwnd = Application.hwnd

'Get the window's position
GetWindowRect lHwnd, WR

'Get the window's device context
lDC = GetWindowDC(lHwnd)

'Get the height and width of our text
GetTextExtentPoint32 lDC, strText, Len(strText), TextSize

Result(1) = TextSize.X
Result(2) = TextSize.Y

GetTextSize = Result

End Function

Sub test()

Dim arr

arr = GetTextSize(String(10, "w"))

MsgBox "text width: " & arr(1) & vbCrLf & _
"text height: " & arr(2), , String(10, "w")

arr = GetTextSize(String(10, "i"))

MsgBox "text width: " & arr(1) & vbCrLf & _
"text height: " & arr(2), , String(10, "i")

End Sub


RBS


"RB Smissaert" wrote in message
...
Three other suggestions.
One is to use a fixed width font, so you can calculate the width from the
number of characters.
Two, to make a VB6 dll that uses TextWidth. This will dermine the width of
your text.
Thirdly, to use the Windows API to get the actual width. Can't remember
the
actual functions now you need, but I am sure it can be done.

RBS


"ranswrt" wrote in message
...
How do I write a code that will not allow a string to be wider than the
cell
that it is going into? The with of the cell is 27.86 (200 pixels). The
problem I have is I can't limit the number of characters because they are
different widths depending on the character.
Thanks