Solved

Control tip, how to put a return character in long strings.

Posted on 2011-03-19
8
305 Views
Last Modified: 2012-08-13
I have several Excel forms and in each of the controls i am popuating the controltiptext with information on teh control.  Is there a way to input a return character in the controtiptext so it is not just one long string?
0
Comment
Question by:ssmith94015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35173499
you can use vbCRLF which is the carriage return line feed code.

Here's a function you can put in a module - it will insert the vbCRLF as the increment you want to make the break - e.g., 50 chars, or whatever.


Code:
Function breakString(sControlTip As String, iBreak) As String
Dim tempStr As String
    
    If iBreak > Len(sControlTip) Then Exit Sub 'no need for vbCRLF as the string is already shorter than that
    
    For i = 1 To Len(sControlTip) Step iBreak
        If i = 1 Then
            tempStr = Mid(sControlTip, i, iBreak)
        Else
            tempStr = tempStr & vbCrLf & Mid(sControlTip, i, iBreak)
        End If
    Next i
    
    breakString = tempStr

End Function

Open in new window


Now, if your string is called sBIGstring, you can set your control tip using....

  resultForTip = breakstring(sBIGstring,50) 'carriage return, line feed every 50 characters...

Enjoy!

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35173511
Whoops - I tested as a sub, then converted to a function...  then, I put the last error checking in.  The Exit Sub statement should be Exit Function...

Here's the revised code:

Function breakString(sControlTip As String, iBreak) As String
Dim tempStr As String
    
    If iBreak > Len(sControlTip) Then Exit Function 'no need for vbCRLF as the string is already shorter than that
    
    For i = 1 To Len(sControlTip) Step iBreak
        If i = 1 Then
            tempStr = Mid(sControlTip, i, iBreak)
        Else
            tempStr = tempStr & vbCrLf & Mid(sControlTip, i, iBreak)
        End If
    Next i
    
    breakString = tempStr

End Function

Open in new window

Dave
0
 

Author Comment

by:ssmith94015
ID: 35173525
In what event do I put the controltiptext statement?  In the form's initialize event?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:ssmith94015
ID: 35173561
I put this in the form's initialize event.  The string does show up, but it does not line-break.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35173598
can you send me a pared-down version quickly?  Or, should I build it from here?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35173610
Never mind - I built one.  Its not looking good.  not sure controltiptext has a word wrap option.  It wasn't designed for that.

Doing some googling and found that folks put labels on the userform with the info needed to guide.  some even "unhid" the labels based on the mouse hovering for a short while.

Would you be interested in going down this path?

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 35173621
This one's pretty cool - put a "what's this" help button on your userform, then you can document to your heart's content...

http://www.andypope.info/vba/whatsthishelp.htm


Let me know if you'd like further assistance with this.

Dave
0
 

Author Closing Comment

by:ssmith94015
ID: 35173793
Thanks!  Sounds like you got the same Google results I did!  what is really weird is several years ago I was creating an ACCESS database form and got it to have multiple lines.  I don't know how I did nor can I find a copy of that old database and form - Naturally!  I will follow up on your link.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question