Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-19
8
Medium Priority
?
311 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

885 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