Solved

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

Posted on 2011-03-19
8
306 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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