Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
string width
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
string width
Have you considered shrink to fit?
-- HTH, Barb Reinhardt "ranswrt" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
string width
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. "Barb Reinhardt" wrote: Have you considered shrink to fit? -- HTH, Barb Reinhardt "ranswrt" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
string width
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 on the right, it will automatically truncate if wraptext is not enabled. "ranswrt" wrote: 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. "Barb Reinhardt" wrote: Have you considered shrink to fit? -- HTH, Barb Reinhardt "ranswrt" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
string width
Thanks
"JLGWhiz" wrote: 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 on the right, it will automatically truncate if wraptext is not enabled. "ranswrt" wrote: 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. "Barb Reinhardt" wrote: Have you considered shrink to fit? -- HTH, Barb Reinhardt "ranswrt" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
string width
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
string width
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
string width
Hi RBS,
What is relative width? Thanks, Dan |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
string width
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 "dan dungan" wrote in message ... Hi RBS, What is relative width? Thanks, Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
String width not exceed column width | Excel Programming | |||
Cell/column auto adjusts width to longest text string | Excel Discussion (Misc queries) | |||
ASC() does not convert full-width to half-width letters (Exel 2003 | Excel Worksheet Functions | |||
fixed column width with text string | Excel Discussion (Misc queries) | |||
String width in specified font/size when merging | Excel Programming |