How long is the text

[bpeirson]

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.

 

[itsmyjob]

May be you can put the text in a Shape with AutoSize = True and read the size (Width) of the Shape.. Then you will know if the text is too long or not.

Eric N.
indocti discant et ament meminisse periti ​

 

[VBSpawn]

Hi,

I'm not sure but it's worth a try :

Private Declare Function PathCompactPath Lib "shlwapi" _
  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

 

[bpeirson]

VBSpawn, if I read this right the TextFun function determines the width of the form on which the command1_click() event comes from. "Me" is the form where the button is.

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.

 

[CajunCenturion]

I would suggest that try to find an object that supports the TextWidth and TextHeight methods. Those methods will return the actual height and width of text taking into account proportional fonts and the current font properties, such as bold, italic, point size and so forth.

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

 

[handleman]

Can you change to a fixed-width font?

 

[VBSpawn]

In VBA it should be something similar to :

Private Declare Function PathCompactPath Lib "shlwapi" _
  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