string width

[ranswrt]

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

 

[Barb Reinhardt]

Have you considered shrink to fit?

 

[ranswrt]

I have, but I would rather stop the string at a certain width. I would have
to go thru multiple sheets and cells and format them to shrink to fit.

 

[JLGWhiz]

You will have to make a sacrifice somewhere, either widen the range with
autofit, use shrinktofit or wraptext. If there is data in the adjacent cell

 

[ranswrt]

Thanks

You will have to make a sacrifice somewhere, either widen the range with
autofit, use shrinktofit or wraptext. If there is data in the adjacent cell

 

[RB Smissaert]

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

 

[RB Smissaert]

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

 

[dan dungan]

Hi RBS,

What is relative width?

Thanks,

Dan

 

[RB Smissaert]

The width of one string compared to the width of another string.
As said, not sure it is helpful, but the code does give you some information
as shown in the example.

RBS