×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How long is the text

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.

RE: How long is the text

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

RE: How long is the text

Hi,

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

CODE

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

RE: How long is the text

(OP)
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.

RE: How long is the text

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

RE: How long is the text

Can you change to a fixed-width font?

RE: How long is the text

In VBA it should be something similar to :

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources