string width

R

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
 
R

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.
 
J

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
 
R

ranswrt

Thanks

JLGWhiz said:
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
 
R

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
 
R

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top