How long is the text
How long is the text
(OP)
I am trying to print information to a form which has a certain width, I am using excel so how do I make sure that the text doesn't extend out of its boundary. I was originally going to use the excel LEN(A1) function but when printing, the letter "I" takes up less space than "W" therefore the number of characters would only give an approximation.
I would like to avoid merging cells and word wrapping.
I would like to avoid merging cells and word wrapping.





RE: How long is the text
indocti discant et ament meminisse periti
RE: How long is the text
I'm not sure but it's worth a try :
CODE
Alias "PathCompactPathA" ( _
ByVal hDC As Long, _
ByVal lpszPath As String, _
ByVal dx As Long) As Long
Private Sub Command1_Click()
Dim myText As String
Dim myShort As String
Dim oLen As Integer
Dim aLen As Integer
myText = Me.Text1.Text
oLen = Len(myText)
myShort = TextFun(Me, myText, Me.Label1)
aLen = InStr(1, myShort, Chr(0))
If aLen < oLen And aLen <> 0 Then
Me.Label1.Caption = myShort
Else
Me.Label1.Caption = myText
End If
End Sub
Public Function TextFun(oForm As Form, _
ByVal sPath As String, _
oControl As Control) As String
Dim nWidth As Long
nWidth = oControl.Width / Screen.TwipsPerPixelX
PathCompactPath oForm.hDC, sPath, nWidth
TextFun = sPath
End Function
RE: How long is the text
I am not trying to put text on a form, I am trying to put text into a cell on a worksheet. How would I transfer the relevant information?
Is it possible to print a userform to a page? I never tried that before. If so it might simplify my problems.
RE: How long is the text
In VB, the form object supports those properties, but that form object may not be available in VBA. There are some textboxes that support, but not all. There are other controls, if memory serves, such as the FlexGrid, that support those methods.
Anyway, look around for those methods in some of the controls that you have available.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
RE: How long is the text
RE: How long is the text
CODE
Alias "PathCompactPathA" ( _
ByVal hDC As Long, _
ByVal lpszPath As String, _
ByVal dx As Long) As Long
Private Sub TestCell()
Dim myText As String
Dim myShort As String
Dim oLen As Integer
Dim aLen As Integer
myText = Application.Cells(1, 1).Text
UserForm1.Label1.Width = Application.Cells(1, 1).Width
UserForm1.Label1.Caption = myText
oLen = Len(myText)
myShort = TextFun(Me, myText, UserForm1.Label1)
aLen = InStr(1, myShort, Chr(0))
If aLen < oLen And aLen <> 0 Then
Application.Cells(2, 1).Value = myShort
UserForm1.Caption = myShort
Else
Application.Cells(2, 1).Value = myText
UserForm1.Caption = myText
End If
End Sub
Public Function TextFun(oForm As Object, _
ByVal sPath As String, _
oControl As Object) As String
nWidth = oControl.Width
PathCompactPath UserForm1.Label1.[_GethWnd], sPath, nWidth
TextFun = sPath
End Function
Private Sub CommandButton1_Click()
TestCell
End Sub
but somehow it looks like depending on the selected font too